[RESOLVED] connecting to a access database

[CWITT]

I have been using vb.net and Visual Studio to connect to and use MS access databases for years with no problems, however yesterday i encountered a problem. I don't know when it started, as i haven't had to add a new database or modify an existing one in awhile...

The problem:

When trying to update a database connection to include new tables, or connect to a new database I now get this error message for every table i have selected..

"Could not retrieve schema information for table or view"

I originally thought there was a problem with the database engine (12.0 x64) but was able to discard that theory by testing other engines (13.0, 15.0, 16.0 x32 and x64). I also though perhaps the database itseelf was at fault, yet all my databases still function "as-is" and none of the new ones I've made will connect.

Can anyone offer some guidance?

Edit:
I'm using VS 2017 on W10 (with the latest feature update, though that shouldn't matter as it was a problem before the update)

[wes4dbt]

When trying to update a database connection to include new tables, or connect to a new database I now get this error message for every table i have selected..

How are you trying to connect? Are you trying to add a datasource to your project, are you using the datasource wizard, what is your connection string?

Need more information.

[CWITT]

String for existing connected databases (that still work):
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;

I do use the wizard to connect to new databases and modify connections to existing databases (both now fail when selecting a "new" table).

Not sure what other info I can add...?

[CWITT]

As you mentioned the wizard (that i normally use), i thought i'd give manual create a try, and ran into this error..

Attachment 153693

not sure what that is, or why it's looking for it..?

[wes4dbt]

That error looks like your trying to connect to a SQL Server database. But that's just a guess, would need to see your code.

I do use the wizard to connect to new databases and modify connections to existing databases (both now fail when selecting a "new" table).

So are you trying to add a new datasource or edit an existing one? Please provide a complete explanation.

[CWITT]

ok, for the 3rd time.. The original error occurs when I try to add a new database AND when I try to edit the connection to an existing database.

The new error that i get when NOT using the wizard occurs when trying to modify the connection to an existing database.

I am not and have never tried to connect to a SQL server (making the new error very odd). I only use MS Access Database files.

Edit:
re code request, there is no code to post beyond the connection string I already posted?

I'm doing this all from the VS 2017 interface, not in code.

[wes4dbt]

ok, for the 3rd time.. The original error occurs when I try to add a new database AND when I try to edit the connection to an existing database.

Ok, for the third time, this is not enough information. At least not for me. There are multiple way of doing those things. I really don't want to spend my time trying to guess what you did or what was the steps you tried that led to the error.

So I will let someone else try and help you, good luck.

[CWITT]

i'm sorry, really. I'm not holding anything back. Short of posting screen grabs of the wizard dialog box i don't know what else there is to give you?

[wes4dbt]

Ok lets try this,

Your say that if Select Project->Add New datasource->then the wizard pops up and you select Database->dataSet->New connection String->Microsoft Access Database File (OLE DB)->then browse and select your new database->then select some table->finish

You get this error "Could not retrieve schema information for table or view"

[CWITT]

exactly.

[wes4dbt]

Is this file a MDB or ACCDB, and can you open it in MS Access?

[CWITT]

Currently ACCDB (though i have tried it as MDB). Yes I can open the file (it's a copy of a file already in use on a daily basis), and I ran repair on it just in case.

[wes4dbt]

String for existing connected databases (that still work):
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;

In this same project, you use this connection string and it works?

When you try to add a new datasource, does it create the dataset but it's empty? does it save the new connection string to your project settings? If so post the connection string.

[CWITT]

just so i'm getting what you are after... you want the info from the failed database add? If that is so, I'll post it shortly (I always stop at the error).

[wes4dbt]

just so i'm getting what you are after... you want the info from the failed database add? If that is so, I'll post it shortly (I always stop at the error).

Also want to know if the connection string that's working is in the same project.

[wes4dbt]

You might want to run a quick test like this

Add a new form
Add a DGV to the form

Imports System.Data.OleDb
Public Class Form8
    Private Sub Form8_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=yourDatabaseName")
                Using da As New OleDbDataAdapter("Select * from someTableInDatabase", con), dt As New DataTable
                    da.Fill(dt)
                    Me.DataGridView1.DataSource = dt
                End Using

            End Using

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub
End Class

Let me know what happens.

[CWITT]

Yes, that is the working string (minus the actual file path) for all my database connections (usually no more than 2 databases per program, if that matters).

Attachment 153703
Attachment 153705
Attachment 153707

I think that covers it..?

Edit:
Obviously this file is MDB (prior tests were done on ACCDB files too as noted prior)

[wes4dbt]

Still don't know if this is the same project with the connection string that works? Also, validate you have the proper references. That's why I keep asking if this is the same project with the working connection string.

I've got to be going.

[CWITT]

maybe that is part of the problem, I am talking about ALL my projects and you think (or seem to) this is specific to 1 project.

All my projects use that connection string. They are all fully 100% functional right now (same goes for the database files). It's only when i try to add one of these existing (as a copy, or an entirely new) databases to another project (be it an existing project or a new project) that I get the errors shown (obviously the names change based on what database I happen to select).

I also get the same error(s) if I have added a new table to an existing database and try to update the connection to select that new table.

I really hope that I'm making this easy to understand..?

My next step will be to uninstall VS2017 and try a clean installation (perhaps the problem stems from a VS update gone wrong? There have been several recently).

[wes4dbt]

You may have a corrupt install. Have you tried accessing the database without using the wizard, like I showed in post #16?

I also get the same error(s) if I have added a new table to an existing database and try to update the connection to select that new table.

Just to be clear, adding new tables to an existing Typed Dataset (datasource) has no effect on the existing connection string. But if the Wizard has a problem then Adding a new datasource and Editing an existing Datasource would be affected. One thing that might provide some helpful information, Add a New Datasource, then after you create the New Connection string, Click the Test Connection button. This problem probably doesn't have anything to do with the connection string. I mean, if the designer shows you a list of the database table and fields, then it has connected.

[wes4dbt]

I Googled "visual basic Could not retrieve schema information for table" found lots of information.

Look at the last post, https://social.msdn.microsoft.com/Fo...orum=vbgeneral

That does seem to fit with the error you posted in #4

[CWITT]

ok, in order..

I did run your test, and while the tableadapters created by the wizard when it was still working are able to read/write the database your test code could not.
Attachment 153815

I don't think i ever mentioned the connection string when i was talking about adding a new table to an existing database connection, but we are in agreement here. perhaps it was just a misunderstanding.

all the existing connections and even the new ones that result in the above error all say the "test connection" is successful.

I reinstalled VS2017 with no noticeable benefit, the errors remain.

No. But putting as-side the fact that that post isn't about the OLE connections, the "fix" noted did nothing for me.

[kpmc]

Are you able to open the database with access?
have you tried copying the database to a local path and connecting without the UNC path, maybe there is a NTsecurity problem.
Have you tried repairing the database?
Also try using JET instead of ACE
IE:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DATA\TEST.mdb

[CWITT]

yes i can open them all.
location doesn't matter i have local & network database files. all the same results.
yes I've done repairs, just in case.
JET isn't an option on x64.

[wes4dbt]

FYI - Jet is an option on x64 machines. Just go to the project properties->Compile->Target CPU = x86 (this is for VS 2015). Or did you mean you want to Target x64?

[CWITT]

yes, you can use jet is you compile for x86. I don't. Ever. All my coding is done on and for x64.

[CWITT]

I'm at a total loss as to why this is happening. I've even ruled out the computer itself, as I have a second computer doing the exact same thing.

The only thing different between them is the MS Access version installed (though that shouldn't matter anyway?).

Guess next I'll try an older version of VS.

[CWITT]

The problem is VS 2017, one of the many recent updates broke it. got 2015 installed and all is good. Now I just need to figure out how to get 2017 without the damn updates..

Ideas?

[wes4dbt]

Glad to hear you got a working version VS. It's strange that the updates broke your VS, seems like if there was a bug in the updates there would have been other people mentioning it on this forum. The fact that this is happening to you on multiple machines makes it even stranger.

[CWITT]

sadly, I have a habit of finding bugs others don't. it comes in handy when testing alpha/beta software.. but very aggravating when working with production software.

Edit:
I'm also using the free community versions, not the pro/ent versions. that may make a difference.

[CWITT]

For those who care, Microsoft was able to provide me with a earlier version of 2017 (15.3.5) that works.

Ty wes, for helping me narrow it down.

[palimpodio]

Hi, everybody.
Visual Studio 17, Access 2010, Windows 10 32bit.
Had same problem and after having tried everything, solution came only after installing 'data storage and processing' component (or something like that, obviously not in English in my case) from Visual Studio Installer. Guess some critical SQL connector was put in right place after that. Hope it helps someone.