VBA Macro - OLE Database Connection Error (Office 2016 Update Issue)

[Anonymous]

This is a followup post to the following issue that was previously resolved.

http://forums.autodesk.com/t5/inventor-customization/vba-macro-ole-database-connection-error/m-p/441...

I recently installed Office 2016 which caused my VBA macros to stop working.  I am receiving the same provider cannot be found error as before.

Run-time error '3706':

Provider cannot be found.  It may not be properly installed.

'Open connection
With oCN
       .Provider = "Microsoft.ACE.OLEDB.12.0"
       .ConnectionString = sConnection
       .Open
End With

After some research I changed the provider version from 12.0 to 15.0.  This didn't correct the error.  I then passively reinstalled the AccessDatabaseEngine.exe solution.  This also didn't correct the error.

Any ideas on how to fix this?

[adam.nagy]

Hi,

- Did you install the 64 bit version of Office 2016?

- Also, did you install the 64 bit version of the AccessDatabaseEngine? "AccessDatabaseEngine_x64.exe" (based on the file name you mentioned it could as well be 32 bit)

Cheers,

[Anonymous]

Office install is 32 bit running on a 64 bit machine.  I have installed both the 32 bit and 64 bit versions of the Access Database engine.  Still have the issue.

I don't believe that the reference Microsoft.ACE.OLEDB.16.0 exists.  If it did I think that would fix the issue by simply changing the reference. 

[adam.nagy]

Hi,

Did you try to get help on any Microsoft Access forums? - I assume that this issue is not Inventor specific and you would run into the same from any 64 bit application, including standalone ones. You could give that a try by e.g. creating a 64 bit app in Visual Studio (if you have it), or from another 64 bit product.

Also, did you install the 2016 version of the Access Runtime? I think this one is it: https://www.microsoft.com/en-us/download/details.aspx?id=50040

I was searching for the connection string you are using (Microsoft.ACE.OLEDB.16.0) in context of Office 2016, and found this: https://community.qlik.com/thread/185575 - so maybe using the 2010 driver and conection string would be a solution?

I also found this concerning another system: http://www.nicelabel.com/?t=faq&id=255

The key point is that you install 
Microsoft Access Database Engine 2010 Redistributable of the opposite "bitness". 

I hope this is of some help.

Cheers,

[Anonymous]

Adam,

I have not tried to get help on any of the Microsoft access forums.  I agree the issue is probably not Inventor specific.  The 2010 connection string is  Microsoft.ACE.OLEDB.12.0 This is what I was using before without any issues.  Just had to passively install the 64 bit version of the Access Database Engine.

The links you provided are helpful and I will try the suggested solutions and report back.  I was using this in a VBA macro to pull user initials out of an Access database.  I got around it temporarily by having one of our developers write a function to pull it from Active Directory instead.

Thanks.