Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan · ‎02-19-2021

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

PeteJordan · ‎02-22-2021

Thanks for the ticket. The issue is, IT would have to do the change company wide for the 64 bit and that is out of my control and can't see them doing this just for a few of us. I'm sure there's a reason they have the 32-bit MS Office installed over the 64-bit, but again, that's a IT thing here.

Hope this gets working at some point, I know a lot of people were excited to finally have Pro access Access like as in ArcMap...

LanceCole · ‎02-23-2021

Here is the rundown from ESRI on this.

ArcGIS Pro is a 64-bit platform therefore you must have the 64-bit version of Microsoft Access Database Engine 2016 Redistributable installed to see the Access drivers in the OLE Data Link dialog. If you want to use Access on the same system you would need to use the 64-bit version of Office or Access. You cannot install the 64-bit version of Microsoft Access Database Engine 2016 Redistributable while running 32-bit version of Office. This will break Office and ArcGIS Pro will be unstable when trying to use the drivers. There are two options:

Install a 64-bit version of Office.
Don't install Office. You only need the 64-bit version of Microsoft Access Database Engine 2016 Redistributable installed to use an access database in ArcGIS Pro. You could use another system to edit the Access database. Alternatively setup a virtual machine without office and access that from your normal system.

PeteJordan · ‎02-23-2021

Well thanks for getting back again with this Lance. I know option 1 is out of the question and I assume it's because we probably have a lot of legacy things that would make it too time consuming to migrate over to the 64-Bit MS Office 365 which is out of my hands.

That leaves the question I will have to look into if one can actually install 64-Bit Access separately. I think back in ArcMap days I was able to do this when originally ArcMap wasn't able to read the newer *.accdb format now that I think about it. I will check with IT on this though and if that is possible, then I think it might solve the issue then. At least it's worth a shot then. If not, I'll have to look into other options where we can edit live data and have it immediately displayed on Pro as was the case with ArcMap...

Thanks again...

LanceCole · ‎02-23-2021

Please keep in mind that the 64-bit version of Microsoft Access Database Engine 2016 Redistributable should only be installed if you do not have ANY 32-bit Office products installed. This will also cause issues in other Office suite products, such as Excel, Outlook, Word, etc.

I have ArcGIS Pro installed on one of our servers to process nightly scripts with only 64-bit version of Microsoft Access Database Engine 2016 Redistributable installed - No MS Office installed. I can utilize .accdb and .xslx files without any issues. However, I cannot access these files on our desktop systems with 32-bit Office installed. Fortunately, we have licensing to both versions and IT installed the 64-bit version of Office when ever we install ArcGIS Pro.

A possible third option, would be to generate a script that would run on a regular basis to import your Access database (or selected tables) into file or enterprise geodatabase tables.

Best of luck...

PeteJordan · ‎02-24-2021

Ok I guess it's not the same as the issue we had rolling back just Access with the ArcMap and *.accdb issue back then. I think we can use the current MS Access database and just have it linked to tables on SDE. This way we have the front end and then can see those tables in Pro. The only drawback is newly created datasets or ones that need schema changes wouldn't be able to be done on the fly and would have to pass through the SDE managing department first. At least it's something.

Many times we want to see the updates immediately so the scripts unfortunately won't work, but the linked SDE tables should solve a good chunk of the issues though. Thanks for letting me know about the 64-bit Access not being able to be installed when any 32-bit MS Office is installed. I will document that as well...