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

[XanderBakker]

Hi Ted Kowal , thanks for sharing this information.

What you will see in a lot of organizations is that they will implement an EGDB and integration with other systems through services. With Enterprise and the Web GIS pattern, you can set up a portal and publish content and apps and share those with specific user groups, by this enabling geography to users that normally don't have access to this type of information. Configuring specific apps for them can be very valuable. The recent announcement of being able to activate an "unlimited" amount of named user of level 1 with Enterprise can help to implement the system of collaboration in the organization. This may be a huge step, but it is one that is aligned with current innovation of the platform and where many organizations are heading. I understand that this change is not something done easily, but the current implementation you have will probably not be very aligned with the future changes. Perhaps Esri will include support for the more recent version of Access files (.accdb), but I haven't seen any announcement related to this format, when there are a number of other ("real") databases that will be supported. 

[TedKowal]

Xander  I do not disagree with you. 

But many small to mid size organisations can hardly keep up paying for Licensing and Maintenance.  Like mine, I have tried to implement a larger scale environment, but do not have the support or funding necessary to do it, not only the software, hardware costs , there is the increase in staffing to support it.  My Web based mapping, to date is using Bing Maps and their api on which I overlay our GIS layers.  Since the number of hits to the app I created is below the threshold for licensing -- it is free to us.  In order to make that function-able, yes I had to create my own portal (really a service) to work with the GIS data.... again to automate the update process, I have created MS Access Scripts to update the SQL WKT fields from our GIS.  I had to overcome the limitations ESRI put on SQL (free version) will only re-write the GIS table, I am now able to read our GIS and update the SQL using my own code logic.  My org chose not to fund the upgrade in licensing for SQL server, and Arc Server.....

In the end I still have a serviceable albeit not scale-able web map that users can search, even update in limited fashion some GIS elements -- all using the BING API and SQL Sever built-ins and some custom programming.

[XanderBakker]

Hi Ted, 

I think you have managed to come a long way with the limited resources that are made available to you. Kudos for that. It is understandable that you seek for a way to be able to use new versions and software like ArcGIS Pro, but that you also need to be able to use it in conjunction with other software that you are using. I don't know what type of company you are working in, but in some sectors there are "small ELA's" available which include most software of the platform. Not sure is that is available to your company. 

In case it is not, sometimes a AGOL subscription can be a good light replacement for Enterprise to solve part of the challenges you are facing.

[TedKowal]

I work for a toll way transportation authority (Something in between the State and the County Level).  The tollway operates under minimal staffing and consults everything out.  There is no data standardization possible, yet, I am still working on this (past 9 years).  The Authority does not store its own data, but relies on different consults to do their own independent data stuff, so our data can and does reside somewhere on the WAN in numerous "real" databases.  None which talk to each other, my challenge is to work with the existing system, there is no foreseeable change, to bring this data together.  Sometime there is only a spatial linkage that can relate this data together, this is where I use GIS.  Also, this is where "the little database that could" shines in its ability to reach out to all the other sources and bring them together.  

Our procurement process does not allow to have this come under one roof so adopting EGDB is near impossible.  I have managed using the little database that could to link these disparaged sources together....the ability I  have in using GIS to relate them is GOD sent!  A file geodatabase cannot even approach this, nor can individually, SQL Server, Oracle or any other "REAL" database I have worked with do this without enormous extra cost and staffing.  The State of Florida employs your typical EGDB environment, I am local in Miami and use a lot of their data, they, FDOT, have paid me many visits to see how I grab and integrate their data, along with other consultant data and integrate them within our system with a staff of one person.  They have told me over and over that what I do is impossible....  my only trick is the little database that could!  Over the last nine years, I have seen Florida resorting more and more to MS Access as part of the solution due to its flexibility and compatibility with other applications and programs even with their integrated systems.  Now ESRI wants to take away that flexibility and replace it with models that require extra licensing to be of any use.  A file geodatabase is not a database where as MS Access is real close, and does contain most of the elements that define what a database should contain.

A posible alternative to all of this is creating a proper fully compliant ODBC API for the file database.  Your current experimental one is too buggy and does not even contain a full SQL syntax. (I have been looking and playing with it as a possible alternative to MS Access) but alas not even MS access can recognize it much less other application that I use.

[ThomasColson]

Don't forget adding the overhead of having to install and maintain a Portal. I'm assuming due to legal/privacy/sensitivity concerns, you're not using AGOL, which is a reason why most organizations cannot move all of their data to "the cloud". 

Back on topic however, I was where you are...a few years ago, with the same constraints. You DO have a couple of options that could get you some enterprise capability without the cost/overhead of Portal . They boil down to on premise PostGRE/PostGIS, perhaps with a QGIS thrown it. It will do everything you're doing now, with a lot of automation, but a steep learning curve (or you could pay Boundless to set it up for you). You could go SQL Express, and hang a bitty little GDB off it. It too, will continue to do everything you're doing now, and keep your Access front-ends, and you could probably get it for free. If you really want to go rogue, CartoDB (Carto, now) has some very robust automatic import/consume API's and reasonable prices, again, with a steep learning curve, but will do everything you're doing now. 

[TedKowal]

I have been looking at QGIS and have in some isolated instances been using it.  Yes your right and the learning curve is steep and it is still compared to the ESRI product limited.  I also have a PostGIS installation and have been playing around (no production) with it -- My impression, probably because I am still learning, is it is like an older Oracle database (what it use to be).  What initially brought me to ESRI was its openness to data --- give me your data from anywhere and we will work with it.....now it is turning into buy an SDE license and we will only allow you to work with this data....  I am just ranting, maybe it is just a better business model -- everyone has the right to make money?

[IvicaSkender1]

Hi,

one of our users has table data in Access and needs to be able to use those as a source of tabular data that they would associate with the GIS database. I am having trouble trying to see mdb or accdb files from ArcGIS Pro; I managed to make MS Access 32 bit ODBC driver listed among 64 bit ODBC's, but I still can't see any mdb or accdb data source in Pro, not even from tools that are documentedly supposed to use OLE DB data sources. Am I missing something or ArcGIS Pro does not connect to MS Access tables at all? Are there plans to do so? I am not advocating that spatial data should or should not be supported as personal GDB was earlier, just need tabular data be accessible in Pro.
I am using Windows 10 and a recent version of ArcGIS Pro (2.2.4). 

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

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

[BrianE1]

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