Connecting MS Access to ArcGIS Pro

[KellieLinville]

I recently began using ArcGIS Pro as some of our clients have been requesting the change from ArcMap to Pro. We provide a MS Access file which previously was connected to a personal GDB. Its my understanding that Pro does not support personal GDB.

I've never used a ODBC. How would I link the data between MS Access and ArcGIS Pro? We will have users that will work only in ArcGIS and some in only MS Access. So I can't have the data read-only in either.

Any direction would be greatly appreciated!!

[IanKnight]

Hi,

If you are currently storing your data in an Access database, I would seriously consider moving that data into MS SQL Server Express, a freely downloadable lightweight version of SQL Server which can be run on a client PC like Access. ArcGIS connects-up to SQL Server Express very easily.  You can still use Access forms and reports to view and update your tables in SQL Server, Express by setting up an ODBC connection between the two.  If you later need to upscale onto a full-blown SQL Server solution for your data, that would then be easier too. 

[TedKowal]

Still cannot edit and update SQL Server Express so it is useless for Access Users. (Desktop client)

[George_Thompson]

You cannot edit SQL Server data in a non-geodatabase via the client: Geodatabases in Microsoft SQL Server—Manage geodatabases in SQL Server | ArcGIS Desktop 

• Geodatabase data can be edited in ArcGIS Desktop but database data cannot.

ArcMap Help: Geodatabases in Microsoft SQL Server—Help | ArcGIS Desktop 

[TedKowal]

Incorrect.... my SQL Server express/light ONLY allows for a drop table and it creates a new table.  NO editing or updates are allow except for a complete rewrite of the table within the esri desktop.  Although I can from outside of esri environment edit the data.  So updating and existing  table entails re-writing the complete table.  (I am running ArcMap 10.5.1 with the arcinfo license.

[IanKnight]

I often feed my spatial data into SQL Server via batch loads from .CSV files or interactively via an Acess Forms front-end instead of via the ArcGIS client, so the SQL Server connectivity is great for that purpose but yes, I conceed that not being able to insert / update / delete into SQL Server directly via the ArcGIS client is going to be a stopper for some.  

[KellieLinville]

Does the data need to be stored in a geodatabase? For small projects, Access could link directly to the dbase file. I just tested it in ArcPro and QGIS with success. I added the shapefile to my map and was able to edit in GIS (Pro and QGIS) and Access front end. Are they problems with going this route? 

[TedKowal]

You are correct, you can store your geometry in dbase files and edit them and meanwhile use Access as a front end.  However, you are stuck with the limitations of  DBASE IV and that is a show stopper in most cases..... I have not only the layers geometry in access but annotations, relationships, queries( access version of views) ... these are all items in use which DBase does not support.  Additionally other internal programs and web applications that currently hit the access database, falls quickly apart managing the DBase IV style of ODBC access.... any updating in additions forces security management ---- DBase at an enterprise scale (even a small one like mine) falls apart very quickly where as Access can minimally hold its own.

[KyleGonterwitz]

I've thought this over for a minute and I think I would try export to CSV from Pro then import to access.  To go multiuser with personal geodatabases via linked tables in other access databases can be done if you contend with locks,and don't mind occasional lost edits or crashes (situations where a versioning approach would have an edit conflict), and have good file backups - but would be best done through an enterprise platform.

In a one-way direction csv should work.  If I needed cheap and easy multi-user edits I'd look at ArcGIS online something like Survey 123 for forms, Collector apps, or a hosted feature service solution, then export that data to csv routinely for reports or applications built in access.  

[ErikLash1]

I'm leaning towards 64 bit MS Access as the FE for SQL Server.  ArcGIS is meant to work with the architecture of spatial things. And that it does well.

But it has never done the data component of spatial "data" well and it seems to have less and less capability to work with 'data' as the software matures. We keep losing functionality that worked (works) so well in favor of what?

MS Access is a superb FE regardless of whether the data is in an MS Access container or linked from SQL Server into an MS Access container. As Tom mentions above, if the worry is that users might accidently cause havoc with the ESRI columns, create Views, or create data entry forms for the MS Access data. 

ESRI has the proper replacement for the pGDB already, in SQLite, but for whatever reason won't push it forward into production. And the fGDB is absolutely just a virtual paperweight for real data applications (black box with no gateway except ESRI tools) - can't connect to it to run standard SQL queries with anything other than QGIS (limited) and one or two independent projects (that work very well imho) pushed forward in GitHub.

We have a 10 year minimum life-span on some spatial data apps - the solution certainly isn't to create "geodatabase relates" or ArcMap joins in proprietary software, knowing full well they will break when ESRIs stated update schedule make it incompatible.

The longevity solution for us is to use a proper container (in this case SQL Server), run data updates and maintenance in pure SQL (not sql in python), and join the non-spatial tables to the spatial framework through key fields just like we were doing in the 1990s.

We still use ESRI proprietary Geodatabases on SQL server because they have features that work well (versioning etc).  But we also attach to those SQL Server databases with MS Access and allow users to work with the data tables or just work with them in SQL Server Management Studio directly, which also works very well. 

Office 365 is pushing default installs of 64 Bit products.  There is no getting around migration to 64 bit compatibility.  ArcGIS is already a bit late to the party on providing compatibility for MS Access 64 bit. Upvoted the suggestion.

[TedKowal]

Well said!   Don't forget to vote here if you have not already.  Enable ArcGIS Pro to access ESRI Personal Geodatabases