nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

PeteJordan

So has there been any more recent word on this topic? We use a few databases through Access because we have developed complex forms to easy user input which then we need to see on the fly in Arc as the forms get updated by their lat/long.

Last thing we need to do is to spend months waiting for someone to develop a front end for these databases to act the same way as Access does and to be able to fully support it so that we can use it in Pro.

It would be a lot easier for us to have Pro be able to read and access *.mdb or *.accdb as ArcMap has...

TedKowal

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

ErikLash_HiCo

As an aside, watch out when working with 32 bit architectures.

Got bit recently when scripting work across Pro and Desktop through 32 Bit personal geodatabases. I now have to keep 3 separate Python environments running to undertake simple workflows:

32 Bit Desktop 2.7
64 Bit Desktop 2.7
Pros 3.6

In order to work with Personal Geodatabases in script you have to switch to 32 Bit Python environment, which doesn't come up automatically if you have 64 Bit Geoprocessing installed.

ErikLash_HiCo

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.

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.

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.

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

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.

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

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

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.

BrianE1

Pro needs to have enable a connection to a MS Access database. 100% agree!!

JoshuaBixby

As much as I agree that MS Access file formats are not the future, I would also argue neither is Esri’s file database format. The file geodatabase represented a trade off when it was first introduced, i.e., give up data portability and a lot of SQL support to get better performance and support for larger data sets. There was a period of time where Esri could have taken something good, the file geodatabse, and make it a complete replacement for the personal geodatabase. Unfortunately, Esri has done next to nothing to address the initial shortcomings of the file geodatabase.

As much as Esri would like to move everyone to web GIS, there will always be a need for a file-based data store, not just for data transfer but for data use as well. I would like to see File Geodatabase 2.0, the next generation of file geodatabase released publicly/openly (no, releasing a gimped API doesn’t cut it) and implemented on top of an open data format like SQLite. Ironically, Esri has basically already implemented a file geodatabase in SQLite with the Runtime geodatabase, but they keep restricting native access to it through desktop clients.

File-based geospatial data storage is dead, long live file-based geospatial data storage.

XanderBakker

Hi Ted Kowal , could you provide a description of a specific use case that best describes the need you have for Access? I'm trying to understand what kind of situations you are dealing with and if there could be a possible workaround.

TedKowal

Don't matter which version ESRI chooses to support. The biggest benefit that Access pocesses, which overcomes all limitations, is its compatibility with all MS products and myriads of other third party products. That is a benefit the file geodatabase cannot come remotely close to matching. Since the File Geodatabase is not compatible with any other other product on the market outside of ArcGIS, it is useless to me. GIS is a tool for me to manipulate and extrapolate data, maps and geometry are only a small set of tool for me to get at that data by different methodologies.

ThomasColson

You can make a view in your SQL DB that does not include the shape column, then using Access Linked tables, disable that feature class attribute table in an Access Table, Form, or Query.

XanderBakker

If the end user looks at the data using Access, they will not be able to see any geometry. Normally a map can provide much much more information than a plain table. If those users don't have access to Desktop software, you could consider publishing the data in ArcGIS Online and configure a web and have a web app consume that web map. There are many configurable templates available to would enable the end user to benefit from the geography related to the information contained in the Access file / personal geodatabase.

Some steps involved in this process:

KellieLinville

I am open to other methods, but I need recommendations to do so. We work with clients that have little to no experience with GIS so they want to be able to view their data in a user friendly program like Access. I want the data to be able to be viewed/edited in Access and viewed/edited/added/deleted in Pro. Any suggestions? I understand a Pro to Access connection via personal GDB isn't going to be an option.

XanderBakker

I have seen that this topic is important to several people, but you have to keep in mind that the .mdb file format was replace in version 2007 of Access by a new file format with new features and enhancements: Which Access file format should I use? - Access .

Since the personal geodatabase has many limitations compared to the file of enterprise geodatabase (A comparison of geodatabase types—ArcGIS Help | ArcGIS Desktop / Types of geodatabases—ArcGIS Help | ArcGIS Desktop ), Esri has moved away from this format. It is not the future and I recommend you to reconsider the way you are working.

TedKowal

I am in the same predicament. Please vote to re-enable MS Access personal geodatabses here Enable ArcGIS Pro to access ESRI Personal Geodatabases

My only current work around is not to use Pro and stick with ArcMap/ArcGIS as long as possible.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.