CODEWITHSUNDEEP
c#visual-studioms-access
DamnGroundHog
DamnGroundHog

Reputation: 67

C# connection to Access Database fails. Research points to 32-Bit Access with 64-Bit Project, but solutions didn't work

Just starting my first C# project on Visual Stuido 2019. On a Windows Form, I wrote a small connection program with the Access Ace.Oledb.12 connection string. It doesn't work.

conn_string = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = c:\\DBase\\customer.accdb; Persist Security Info=False;";
oledbconnection conn = null;

conn = new oledbconnection(conn_string);
conn.open();

The code is basic as I am still brand new to working in this environment. Alas it didn't work.

I was getting the error message "The database you are trying to open requires a newer version of Microsoft Access"

This is Access 365

I was doing research and it appears my odbc Drivers for Access are only 32 bit I added to my Active solution configuration x86 for 32 bit but it is still not working

Any ideas as how to fix this?

Seems like a ridiculous problem to have especially with all the updates to new office products

Thanks

DamnGroundHog

Upvotes: 1

Views: 2444

Answers (4)

RobinOlsen
RobinOlsen

Reputation: 33

I have to agree with puckhead - for those of us with limited access to the systems we work on converting the DB to a Access 2000 mdb is the simplest way to approach this issue. I had a similar issue and reviewed the answers in this thread and found this one to be the best. In my work environment I do not have the right to install things on my machine (that is up to our network/security team) - if i had time to wait then the ACE suggestion above would also be good but submitting a ticket and getting a response takes time in any organization and if you do not have that time then converting to 2000 (mdb) is the easiest and fastest way to resolve the issue as long as said conversion does not corrupt your data. To do that open your DB and 'SaveAs' a Access 2000 database. Afterwards I was able to connect to the DB using the visual tools in VS 2019 without further issues.

Upvotes: 0

puckhead
puckhead

Reputation: 49

What worked for me in VS 2019 and O365 was to copy the Access database to a 2000 version with the .mdb extension and then modify the connection string as follows ...

Change:

connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ProductionData.accdb;Persist Security Info=True"

To:

connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\ProductionData.mdb;Persist Security Info=True"

Upvotes: 1

Albert D. Kallal
Albert D. Kallal

Reputation: 49309

Ok, a few issues.

It “strange” that you receiving some type of format error. If your connection string was using JET, then this would make sense. But, being accDB format, then the error suggests that some 2010 features are imbedded the application. This often suggests that the new table triggers and store procedures that were introduced in 2010 may be the problem here (2007 can't open these files).

However, keep in mind that around Access 2013 and for sure 2016 than a simple install of Access does NOT expose the ACE data engine for external use (say from .net).

The JET engine never was required to be installed, since it is included with a windows install – and been that way since about windows 98 days. Thus, JET (mdb format only) never required you install Access (full or runtime) and you never did require to even install JET.

However, the ACE data engine is a different matter. As I pointed out, around/about since Access 2013, installing Access (full or runtime) does NOT install a “exposed” and registered copy of ACE that you can use (say from .net). As a result, you now near “always” have to install the ACE data engine. This requiement NOW exists, and is a change in about 20 years of Access. So, no longer does installing Access now give you a useable version of "ACE" dataengine from 3rd party code, or exteral program use like .net.

You will have to thus install ACE on your computer.

You find a link to the database engine here: https://www.microsoft.com/en-us/download/details.aspx?id=54920

From above, you want to install the appropriate bit size. Thus, ACE x32 bits, or an ACE x64 bits.

And you forcing your .net project to x86 is a good idea if that’s the version of office you are running.

So, as it stands now, just installing Access will allow access to use its copy of the ACE database engine, however, such installs “now” do not install an exposed copy of the ACE database engine for use via external code systems such as .net.

So, you can give the above download + install a try.

I also have a VERY small .net program, one is x32 and the other x64. You can find the link here: https://onedrive.live.com/embed?cid=B18A57CB5F6AF0FA&resid=B18A57CB5F6AF0FA%21101312&authkey=AHFOia_sGZx2MiU

Unzip the two samples. You can launch the x32 version, and browse to an accDB file – the utility will open and display data if it can.

Looks like this: enter image description here And you can run the same sample as x64 bits, and see if that works. If neither of them work, then you don’t have a usable version of ACE installed.

Upvotes: 1

Santosh
Santosh

Reputation: 12353

Define connection string in App.config file

<configuration>
  <connectionStrings>
  <add name="DBCS" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Dbase\Customer.accdb"
    providerName="System.Data.OleDb" />
  </connectionStrings>
</configuration>

In code behind the form using following code. 

string strcon = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; 
OleDbConnection con = new OleDbConnection(strcon);
con.Open();

Upvotes: 1

Related Questions