How to connect C# to Access Database with those problems?

Question

As part of a C# program that i trying to fix I run it on my machine and i got this error:

I figured out that the problem is with my Microsoft Access Database Engine and i install the 64 bit version but i got this message :

and i tried to install the 32 bit version and i got this message :

someone saw something like that and can help me please?

Answer 1

Ok, a few things: First, you have to decide or determine what bit size your going to run your .net application as.

If you chose "any" cpu, or choose x86 then of course your .net application will run as x32. The reason why "any cpu" ALSO runs as x32 bits is because Visual Studio (VS) is a x32 bit application. So "any" means .net will take on the bit size of the host process.

The simple issue is you need/want/better specify the .net project bit size and force this issue. If you don't then you can wind up wasting a whole afternoon. KEEP VERY much in mind that if you decide and are using x64 bits for your .net application? Well, you CAN STILL use the connection string wizards and builders in VS, but when you go to TEST connection, for a x64 bit version the test connection dialogs in VS will fail. This is because VS is a x32 bit application. So, even with x64 bit ACE/AccessDatabase, you find the test connection wll fail. However, if your application is set to x64, then running it will work. So, keep this quirk in mind. So, while VS is x32 bits, when you run/debug the application it will respect your project bit settings, but the connection builders will fail on test (so you can use the connection builders - say in the settings panel from VS, but the test connection button will fail).

So, don't use "any cpu" for your project settings. Use x86, or x64 (ie: the bit size you want and plan to use).

Next up: Normally if you have office (and access) installed, in the past, then this would expose and allow use of the ACE database engine. However, for office 2013 and onwards, installing office, Access, or Access runtime DOES NOT expose the ACE database engine anymore. Because of large amounts of confusing on this issue, for Access 2019, (full or runtime), they have reversed this policy. (so, now once again ACE will be exposed with a Access install).

And prior to 2013, installing Access full or runtime will also work and expose the ACE database engine.

Note that it is recommended that you use the SAME connection string for all versions of the ACE database engine. (from 2007, all the way to 2019). That correct connection string is thus this:

That means for ACE 2007, 2010, 2013, 2016, 2019, we are to use this:

If using oleDB provider then:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test2\test44.accdb

If using ODBC provider then:

Driver={Microsoft Access Driver (*.mdb, *.accdb)};
dbq=C:\Test2\test44.accdb;defaultdir=C:\Test2;

Installing the ACE data engine in place of the larger and heaver Access (or access runtime) install.

Now, assuming you don't have Access installed. And assuming that you dealig with 2013, 2016, then you quite much need to install the ACE data engine.

You want to download the ace engine from here:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Make sure you download and attempt to install the correct bit size version.

Getting around the dialog boxes that complain about office x32 or x64 already having been installed. It turns out that so many bits and parts of office are shared between all programs, that if you install say Excel, x32, then you cannot install ANY OTHER office program from word to power-point or Access as x64. You can install DIFFERNT versions of office, but NEVER mix and match bit size for a GIVEN version of office. This is due to the large amounts of .dll's and shared code between office programs.

HOWEVER, you CAN force the ACE engine to install , and install regardless of the bit size.

Download the ACE engine install (your desired bit size). But now launch the installer from the command line. Add the /quiet switch to the install. This will cause the installer to by-pass the nagging and complains about the bit size.

So, from command prompt, go:

C:\runtimetest>AccessDatabaseEngine.exe /quiet

or for x64, I think its:

C:\runtimetest>AccessDatabaseEngine_64.exe /quiet

(going form memory - but just use the /quiet swtich - it should allow you to proceed with the install.

Last but not least:

You can download my sample utility here: https://1drv.ms/u/s!Avrwal_LV4qxhpdBSWWSP5Ivv38AZw?e=dTVQis

It is a small tiny .net exe file (no install). It has a x32 bit version, and a x64 bit version. You can run/try/test either version to see which bit size of access works, or is installed. It shows this:

If the above utility don't work (x32 or x64 version), then you don't have a valid working ACE database engine installed.