X64 and MS.ACE.OLEDB.12.0 Provider

Question

I have noted many replies to what causes the message "The midrosoft.ace.oledb.12.0 provider is not registered on the local machine" even though it is. The remedy which works is to use an X86 build with my MS 2015 project.

However, I hate to leave it at that. When I try to test the connection under X64 to my *.MDB or *.AECCB database, VS 2015 selects said provider and then says it is not registered. (It is there along with a 16.0 provider.) If I try to test the connection with the *.mdb, it chooses the OLEDB.JET.4.0 provider and says the connection is successful. However, when the X64 build is done, I find otherwise.

Is there any way to get VS 2015 to drop the fixation on 32-bit builds? I do not have Office installed on my technical PC. This issue of access database connection is the one item preventing me from retiring and leaving all the software I supported to my colleagues with 64 bit architecture. Would upgrading to VS 2019 help?

Answer 1

The connection problem described above was resolved after a fashion by:

1. Linking to the 32-bit (mdb) equivalent of the 64-bit (.accdb) database to which I really want my 64-bit program to link. But I do not attempt to build.
2. The resx file for the form with the 32-bit link is text editable. (I was very loath to do this as it is supposed only to be edited with the designer.)
3. The name of the 32-bit database *.mdb and the provider for this connection "Microsoft.Jet.OLEDB.4.0" appear. I changed the name of the *.mdb to the *.accdb equivalent and the provider to "Microsoft.ACE.OLEDB.16.0"
4. Then I rebuilt the executable using this form.

It worked. I informed Microsoft through Git Hub hoping that they would revise the install packet for the 64-bit provider to provide C:\Windows\System32\odbcad32.exe to enable one to have the 64-bit provider seen by Visual Studio as "Microsoft.ACE.OLEDB.12.0". Nothing came of that and I, not certain of my grounds, dropped it accepting my Mickey Mouse text edit solution of my problem.

Answer 2

Well, you can't mix x32 bit software and x64 bit software.

Think of why your Apple II 8 bit software will not run on your new computer.

In fact, think of why windows 3.1 (16 bit software) does not run on windows 10.

So, you can't just out of the blue change the WHOLE processor architecture and expect things to work. If you going to run, or want to run your application as x64 bits, then you NEED to install the x64 bit version of ACE, and then change your project from x86 to x64 bits.

 If I try to test the connection with the *.mdb, it chooses the OLEDB.JET.4.0 provider
 and says the connection is successful. However, when the X64 build is done, I find otherwise.

Yes, the connect says it works, but when you run it will NOT work. The reason is that the "test" connection is part of Visual Studio. Visual Studio is a x32 bit program. So, if you have ACE x32 installed, when you hit test connection it will ALWAYS work, and work regardless if your project is set to x86 or x64.

While VS is a x32 bit program only? It is able to launch (and debug) a in-process x64 bit program. But some of the conneciton builders that TEST the connection? They run as x32 bits during development.

In summary: If you install ACE x64? Then test connection will ALWAYS fail - because VS is x32 bits. But if you installed x64 ACE and ALSO set your project to x64? Then when you run it, then it will work.

In summary: If you install ACE x32. Then test connection will ALWAYS work. it will EVEN work if you set your project to x64, since the TEST connection option runs as x32 bits as does VS.

However, if you install ACE x64? Then test connection will NOT work, regardless of project settings being x64 or x86. This is because as noted, VS is a x32 bit program.

So, if you wanting to run + use ACE x64, then you can use the connection builder in VS, but the test connection will ALWAYS fail - even if you correctly installed ACE x64.

So different processor architectures can't be mixed - and in fact that even includes VS - it runs as x32 bit program, and thus the connection testers no more work then attempting to mix up processor architectures in your code.

Of course the mixing and matching of processor archectrures does NOT apply to what we call managed code, or .net code. And the reason for this is that .net code is compiled into a intermediate lanauge and p-code. At run time an 2nd and additonal compile occures DURING run time, so such code and even .dll's can be used by x32 or x64, or you can even use "any" cpu.

However, given that you using external compiled code that is NOT .net, then such code is un-managed, and has already been compiled to machine code for a given bit size. As a result, your .net code bit size settings MUST match. You can in fact have choosen "any cpu", and the PROBLEM is that then you lose control if your application starts as x64 bits, or x32 bits. When develping inside of VS, if you choose "any", then since VS is a x32 bit program, then ANY CPU will result in a x32 bit running.

However, if you compile using ANY cpu? Then if you launch the windows x32 bit command line, ,and launch the .exe? You get a in-process running x32 bit program.

If you take the same any cpu and launch the windows x64 bit command line, and launch the .exe? you get a in-process running x64 bit program.

So, EVEN with a ANY cpu setting, once that program starts as x32, or x64, then it remains as such, and any 3rd party external (un-managed code) called MUST match the bit size at that point. So while .net programs can run as x32/x64 bits, once they started out as one bit size then any and all routines as exernal libraires consumed also must match the bit size.

So, all in all? This means you want to force your project settings to run at a given bit size if it matter. That means avoid using ANY cpu, and thus you force the issue.

However, just keep in mind that if you force/set the project to x64 bits, then any UN-MANGED code systems (such as ACE) will fail if you attempt to use the connection builder "test" option, since VS and the IDE is running as x32 bits at that point in time.

Edit: I have tiny test connector .exe in a zip file that you can download. Give both versions of the included .exe a try - see which one works.

Link: https://1drv.ms/u/s!Avrwal_LV4qxhpdBOVIuD9P4iJmZhg?e=MqkhlF