topic Re: Arcgis Pro Connecting to MS Access Issue with ODBC in ArcGIS Pro Questions

Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Fri, 19 Feb 2021 15:02:45 GMT

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-files-in-arcgis-pro/#point2). 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

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

JoeBorgione — Fri, 19 Feb 2021 15:12:47 GMT

Does this help?

https://community.esri.com/t5/arcgis-data-interoperability/using-odbc-data-sources-in-arcgis-pro-optionally-including-accdb/ba-p/883706

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

LanceCole — Fri, 19 Feb 2021 15:20:42 GMT

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?

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Fri, 19 Feb 2021 15:45:14 GMT

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...

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Fri, 19 Feb 2021 15:46:05 GMT

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...

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

LanceCole — Sat, 20 Feb 2021 01:45:25 GMT

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.

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Mon, 22 Feb 2021 13:36:28 GMT

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...

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

LanceCole — Wed, 24 Feb 2021 02:32:43 GMT

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.

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Mon, 22 Feb 2021 16:41:31 GMT

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:

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

LanceCole — Mon, 22 Feb 2021 17:21:16 GMT

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.

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Mon, 22 Feb 2021 17:28:03 GMT

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...

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

LanceCole — Tue, 23 Feb 2021 19:34:07 GMT

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.

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Tue, 23 Feb 2021 20:21:41 GMT

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...

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

LanceCole — Wed, 24 Feb 2021 02:29:20 GMT

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...

Re: Arcgis Pro Connecting to MS Access Issue with ODBC

PeteJordan — Wed, 24 Feb 2021 13:12:44 GMT

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...