topic Re: Connecting MS Access to ArcGIS Pro in ArcGIS Pro Questions

Connecting MS Access to ArcGIS Pro

KellieLinville — Wed, 21 Feb 2018 15:04:38 GMT

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

Re: Connecting MS Access to ArcGIS Pro

TedKowal — Wed, 21 Feb 2018 17:34:14 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

XanderBakker — Wed, 21 Feb 2018 18:37:26 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

KellieLinville — Wed, 21 Feb 2018 18:45:54 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

XanderBakker — Thu, 22 Feb 2018 12:00:43 GMT

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:

Re: Connecting MS Access to ArcGIS Pro

ThomasColson — Thu, 22 Feb 2018 12:22:51 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

TedKowal — Thu, 22 Feb 2018 15:40:39 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

XanderBakker — Thu, 22 Feb 2018 18:08:40 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

TedKowal — Fri, 23 Feb 2018 17:24:07 GMT

Currently I have the best of both worlds in the personal geodatabase, I can store the attributes and work with geometry from the same data store from any program I choose to use. My excel, project managment, asset modeling management tools, ms office, load rating application (structural analysis programs many of them), inventory applications...many many more such tools and apps directly read the same ms access database. There is no intermediate steps I have to do or make in order to use the data; ONE Singular datastore for both GIS work and Atrribute/Data Analysis/Retrieval/Storage (Does not matter matter to me if the MS Access is slower). This is why I refuse to use the File Database, because I first have to import in the geometry, work with it (Yes I know it is much more faster and robust than the ms variant), then export the work back into something else my myraid of apps can read, if I made a wrong guess, re-export the data back into a file geo-database and repeat over and over until I got the correct results. The GIS portion is only a step/tool in organizing my data needs. In addition, for other external data they may be stored in enterprise location in Oracle, Paradox, and SQL Sever, again MS Access serves me as a middle broker that can reach out simultaneously query all those stores in one query -- again a one stop shop. I am a structural engineer and GIS is only a tool for my work with DATA.

GIS use of data focuses on geometry, not the management and manipulation of data outside of geometry. I do understand why thing like using stored procedures, user defined function ect are not exposed within ArcGIS and only limitly exposed in programming. Why do I have to repeat and re-invent logic when it already resides in outside database and functions fine. -- Yes I can access stored procedures and pass paramater, and use user functions within Access -- File database does not. And the final insult is some of the stuff I need to import to a file geodatabase, I first have to export to an old fashion shape file with to get the data into a file geodatabse, then deal with the data type, and field name limitations of the dbase III plus format (going both ways back and forth).

Sorry, about the wall text this was not the intent for a simple question asked by the OP. For that I apologize for this slight hijacking

Re: Connecting MS Access to ArcGIS Pro

XanderBakker — Fri, 23 Feb 2018 17:56:52 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

TedKowal — Fri, 23 Feb 2018 18:26:37 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

XanderBakker — Fri, 23 Feb 2018 18:52:29 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

TedKowal — Fri, 23 Feb 2018 19:37:52 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

ThomasColson — Fri, 23 Feb 2018 20:00:07 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

TedKowal — Fri, 23 Feb 2018 21:31:51 GMT

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?

Re: Connecting MS Access to ArcGIS Pro

JoshuaBixby — Sat, 24 Feb 2018 15:35:02 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

IvicaSkender1 — Fri, 21 Dec 2018 18:04:40 GMT

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

Re: Connecting MS Access to ArcGIS Pro

BrianE1 — Sat, 02 Feb 2019 19:10:18 GMT

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

Re: Connecting MS Access to ArcGIS Pro

IanKnight — Tue, 05 Feb 2019 15:36:15 GMT

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.

Re: Connecting MS Access to ArcGIS Pro

TedKowal — Thu, 07 Feb 2019 16:26:58 GMT

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