Arcgis Pro Connecting to MS Access Issue with ODBC

[PeteJordan]

I know recently in Dec, 2020 I think, one could now connect to MS Access tables via Pro.  I've been trying to get this to work and unable to see the ODBC connection to the Microsoft Office 12.0 Access Database.

  First I've successfully made the connection to use Excel tables in Pro via (https://www.esri.com/arcgis-blog/products/arcgis-pro/data-management/guide-to-connecting-to-excel-fi...).  This of course required me to use the 32-bit accessdatabaseengine.exe from Microsoft and doing the silent install on my C:drive which worked.

  Looking at my ODBC Data Connections on my system, I see that in both the 32-Bit and 64-Bit have the MS Access Database listed for *.mdb and *.accdb.

However in Pro, I still do not see any reference to any Microsoft Office 12.0 Access Database which from (https://support.esri.com/en/technical-article/000024144 )I should be seeing.

  Is there something I'm missing here?  We are using MS 365 for Enterprise and in Program and Files it does show that the 32-Bit accessdatabaseengine.exe is installed (as I can now use excel tables in Pro).

Thanks

[JoeBorgione]

Does this help?

https://community.esri.com/t5/arcgis-data-interoperability/using-odbc-data-sources-in-arcgis-pro-opt...

[PeteJordan]

I looked at that, and it seems that method would work to import the tables from Access, however, our access tables are live and constantly update and so I'd like to find away for it to work as it does in ArcMap unfortunately...

[LanceCole]

First, What version of Pro are you running?  You need at least 2.6.

Second did you see @KoryKramer and @JoshuaBixby reply's at Is there any way to view .accdb data in Pro?

[PeteJordan]

Yes, I'm currently running Pro 2.7.1 right now, so it should work, in less the last update messed something up which wouldn't surprise me...

[LanceCole]

Pete, 

I had a little more time to look into this and got it working, however, please see caveat at the bottom.

1) I see you have the Microsoft Access Database Engine 2016 Redistributable installed on your system in addition to MS office?  This needs to match the 32bit or 64bit installation of Office - please confirm.  Once you click the download button you will have the option to select the version or just download both.

2) Once this is installed, I recommend restarting your system.

3) Now when you right-click on Database in Catalog in ArcGIS Pro and select New OLE DB Connection you should have the options for Access

I did get this to work when I had the 64 Bit Version of Office 365 installed with the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable.  On another system with the 32-bit versions installed it did not work.  I will put a support case in with ESRI for a possible bug.

You can download the 64-Bit version of Office from office.com.

[PeteJordan]

Hey Lance,

  Yes this is what I have been doing.  We're using the 32-Bit MS Office 365 here.  I can't use the 64-bit because after installing it, Access fails to open, so I had to remove it each time I tested the 64-bit Microsoft engine.  The 32-bit one allows Access to open, but I do not see as shown above, any of the Microsoft Database Engine options in the ODBC Database Connect.

  I don't have the power to switch over to the 64-bit, as this is managed by the IT dept of the company and we would all need to be on the same type.

  So with that, I'll just take it as a bug as you suggested and thanks for trying to help...

[LanceCole]

Got it to work with 32-bit Office.  However, this is against Microsoft’s recommendation not to install the 64-bit version of Microsoft Access Database Engine 2016 Redistributable with any 32-bit Office products installed.

1) From Microsoft, download the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable.  This will be the AccessDatabaseEngine_X64.exe file.

2) Since you have the 32-bit version of Office installed you will need to install the file using the quiet mode otherwise the 64-bit version will not install and fails with a warning.

3) Open an administrative CMD window in the folder that has the file you downloaded.  You may need IT to install this for you, if you are in an enterprise environment.

4) Type "AccessDatabaseEngine_X64.exe /quiet". This will install the 64-bit version of Microsoft Access Database Engine 2016 Redistributable even though you have the 32-version of Office.

5) You should now see the MS Office 12.0 and 16.0 Access Database Engine OLE providers listed.  I did not need to do a restart.

It is my understanding that ArcGIS Pro requires the 64-bit Microsoft Access Database Engine 2016 Redistributable be installed as it is a 64-bit application even when running a 32-bit version of office.  I now have both the 32-bit and 64-bit version of the Microsoft Access Database Engine 2016 Redistributable installed and no issues yet.  

[PeteJordan]

Hey Lance,

See that's what I've been doing and I'm unable to see that.  I've been doing both on the C drive via quiet mode.  I haven't tried it in /passive at all, but heard that wasn't the best way.  The biggest issue as I mentioned, is when doing the x64 it then prevents Access itself from opening up and causes an error.  This is where I'm at a loss.  So it seems maybe something else is going on in the background then if you can get it to work and I am not able to see that.

I just tried it again and for some reason I was able to see the MS Office 12.0 and 16.0 now.  However, the issue is the same that when installing the 64-bit, I can no longer open access.  Using the 32-bit one I am, but then I can't see the MS Office 12 or 16 in Pro:

[LanceCole]

You are absolutely correct.  I also cannot open the 32-bit version of Access if the 64-bit version of Microsoft Access Database Engine 2016 Redistributable is installed. 

Since you are using Office 365 you should be able to download and install the 64-bit version of office this may eliminate the compatibility issue.  I do not have access to a 64-bit version until this evening to confirm if you can still open Access with the 64-bit version of Microsoft Access Database Engine 2016 Redistributable installed but will check it out tonight.  I know the drivers were showing but I did not try to open Access.