idea Enable ArcGIS Pro to access ESRI Personal Geodatabases in ArcGIS Pro Ideas

Enable ArcGIS Pro to access ESRI Personal Geodatabases

DavidWheelock — Wed, 01 Jun 2022 19:12:44 GMT

Please enable ArcGIS Pro to use the ESRI Personal Geodatabase. PGD's are compact, efficient, and an ESRI standard. PGD's offer easy, built-in interoperability, and table data in a PGD can be easily accessed from any MS Office program or any other program able to read a MS Access database. It does all of this in a single, compact file, without the overhead of an enterprise or workgroup geodatabase which require a database server and without the clutter of a file geodatabase. FYI, we do have an enterprise geodatabase for when we need that.

It's deeply troubling that ESRI has chosen to deny its loyal, long-time customers the ability even to read their data in an ESRI standard format, the Personal Geodatabase.

No, a File Geodatabase is not a suitable alternative or replacement. A FGD is a silo and cuts the data off from most other programs. It isolates the data and can only be accessed from ESRI programs or GIS programs that have access to it and cannot be read at all by office productivity software or any other database software. It's not useful to me.

ESRI's explanation in the ArcGIS Pro docs:

http://pro.arcgis.com/en/pro-app/tool-reference/tool-errors-and-warnings/001001-010000/tool-errors-and-warnings-01301-01325-001324.htm

ESRI states that "Personal geodatabases do not scale well in the 64-bit environment" and will not be supported. It recommends a FGD.

My response to ESRI:

Please let me and your other users be the judge of whether a personal geodatabase suits our needs. We can decide for ourselves when we need to scale to a more robust GDB format. Permanently terminating our ability to use the PGD is not a useful solution for me. Again, a FGD is not a suitable replacement for the reasons cited above.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

DuncanHornby — Wed, 05 Oct 2016 10:48:35 GMT

Agree 100% with this idea, I use pGDB all the time for storing analyses which I can then very easily get out into other applications, usually through an ODBC link. The irony is that forcing us to use fGDB requires us to export out to dBase format which is how many decades old???

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

DavidWheelock — Wed, 05 Oct 2016 16:13:28 GMT

Thanks, Duncan. It's actually shapefiles that use DBase 4 format. That's older than fGDB and pGDB! As far as I can tell, fGDB is completely proprietary to ESRI, though I think ESRI has published the spec, as they did with the shapefile, so that it can become a common medium of transfer.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

KellyAlfaro_Haugen — Fri, 26 May 2017 21:01:23 GMT

In addition you can no longer import dBase files into Access (2013 or newer) so the only way I can easily talk between my GIS tables and Access, without a ton of reformatting, is to store it as an pGDB (.mdb) and then import into Access or save it to an older version. There is so much communication I do between tabular data (coming and going from access/excel users) and my GIS files. That will be lost if they cut pGDB. FYI excel and txt does not cut it.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

MattWilkie3 — Thu, 15 Jun 2017 22:17:22 GMT

Esri has published an API for the file-geodatabase but not a specification. There are significant aspects of the f-gdb format that are not exposed in the API, and thus not available to any would-be 3rd party applications/developers.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

JenniferBaughman1 — Fri, 16 Jun 2017 15:33:32 GMT

Further, FGDBs only support a restricted subset of the SQL language, and some parts of the subset are only accessible through ArcObjects (using their PostFixClause attribute). You can't, for example, use ORDER BY or DISTINCT to select by attribute with an FGDB, but you can with a PGDB. PGDBs aren't perfect, but they do have their uses.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

VinceAngelo — Fri, 16 Jun 2017 16:38:10 GMT

Shapefiles are actually based on dBase III+, not IV, though ArcGIS doesn't use a strict interpretation, since 255 fields are permitted, even though the specification limits dBase III to 100 fields. "dBase III+-ish" might be a better name for it.

My understanding is that the problem with Microsoft Access-based Personal GDB support is the limited functionality provided by 64-bit Access libraries (Pro is a 64-bit app, so 64-bit libraries are needed). "Do not scale well" doesn't mean "It's a bit slow" or "It's kinda slow"; it's closer to "It's so slow, my grandchildren will need to see if the build verification script ever finished."

If Esri were able to get an improved 64-bit Access library which was "only" four times slower than 32-bit access on the same host, and then added it to Pro, would you thank them for giving you a chance to evaluate it, or file bug reports about the performance?

- V

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

MattWilkie3 — Mon, 19 Jun 2017 16:10:40 GMT

If the docs were clear about what to expect from the driver and what upstream limits drives the limits here then no, I wouldn't file performance bug reports. Thanks for telling us some of what is involved.

I think this strays from the meat of the issue though, which courtesy of @Duncan is "I use pGDB all the time for storing analyses which I can then very easily get out into other applications, usually through an ODBC link.". The real desire is to use Esri-ignorant tools to get at and manipulate portions of the content in a geodatabase that isn't possible or is inefficient using the Esri tool kit and APIs. Requests for continued Mdb and Access functionality are one expression of that central aim.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

PeterRussell — Thu, 10 Aug 2017 23:19:25 GMT

Anyone who has any non-gis enterpise databases that have some sort of relationship to the GIS data will almost certainly be using personal geodabases. Yes you may use file geodabases to do the gis manipulation - it is a lot quicker - but you then move the data to a personal geodatabase, fire up MS Access do all the cool things in there and then move it back to the file geodabase if needed. I have a fair number of pythons scripts that do things like this. One in particular copies data from a SDE geodatabase into a pgdb then runs a MS Access macro that uses just under 80 queries to modify the data by joining to a number of corporate databases before moving to a file geodatabase. To me, this is the sort of workflows and functionality we lose with no personal geodatabase support.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

ChaimSchwartz3 — Wed, 29 Nov 2017 04:59:14 GMT

Just wondering, since we're considering similar stuff, why you won't connect directly from Access to your enterprise (Oracle?SQL Server?) via ODBC and run your queries, and thereby skip the pGDB step?

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

RickMorrill — Sun, 31 Dec 2017 18:24:34 GMT

David and the other commentators are right on mark with the issues presented by a loss of the ability to connect between Arc and MS Access in the current version of ArcGIS Pro. ESRI should be willing and able to support the many users of Personal GDB's if they want us to migrate to Pro.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

RosieYacoub — Fri, 05 Jan 2018 18:31:10 GMT

The update query and multilevel joins that can be handled through MS Access far exceed anything ESRI has so far. If ESRI can give ArcGIS pro the same query tools MSAccess does: a UI that permits temporary joins across multiple tables and keys, allows for group by queries on any combination of attributes, allows for dependable query speed under one second for over 50,000 records--then they might be able to let go of the PGDB. Or if they provided an ODBC driver to the file geodatabase....

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

TedKowal — Fri, 12 Jan 2018 17:09:29 GMT

I am using 64 bit MS Access libraries in various other applications that require 64bit access including the buggy 64bit odbc drivers..... I have not noticed any appreciable speed changes?

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

TedKowal — Fri, 12 Jan 2018 17:12:49 GMT

The ODBC driver would have to have a "FULL" SQL implementation! The current pGDB is very limited on the SQL side.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

BrianBulla — Tue, 30 Jan 2018 14:58:38 GMT

I also agree that the PGDB format needs to be supported in ArcPro. We have many (ie. 1000's) of MXDs that have been created over the years that have PGDB connections in them. We find the PGDB much more user friendly than the FGDB format, especially if you want to get into the back end of the data and do some advanced querying that you cannot do through the ArcMap/Pro interface.

What are we supposed to do? Convert all of our 1000's of PGDB files into the FGDB format?? That is not practical.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

TedKowal — Tue, 30 Jan 2018 15:27:16 GMT

I am running access 2013 and I have no problems importing dbase files? BTW why do a double import .accdb holds old .mdb tables very well as links? The whole point about using a personal geodabase is that .mdb file is compatible with a countless other applications and process without having to do any additional work. The newer access versions link very well to older dbase files, sql server, oracle, post gis and countless other types of databases. Not to mention directly compatible with countless other programs. The esri file database is ONLY compatible with ESRI products. The power and mystery that brought me to ESRI was its compatibility with DATA. I also have been working with the ESRI 64bit drivers for access and have found them OK and work reasonable with reasonable speed unlike vangelo-esristaff seems to infer. Additionally, the reason for Access is not for speed, but for compatibility and the lessing of the work effort to reach the end result. So even with slower speeds, I reach my end point much quicker with fewer mistakes due to many black boxes resident in the ESRI formats.

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

VinceAngelo — Tue, 30 Jan 2018 21:58:07 GMT

I did not do the testing, but I was told about it, back when initial 64-bit Server testing was performed (during 10.0 development), by someone who had a cause to know.

If you haven't tested with ten million rows in point, line, and polygon tables, with vertex densities from 100 to 50k per feature, side by side with 32-bit arcpy, with both local and networked data sources, then we really don't have a basis for evaluation of whether the modern drivers are a significant improvement.

I have an ArcPy script I developed for benchmarking the break-even point for querying rows using a where_clause vice testing row contents in a DA SearchCursor, using a series of logrithmically increasing table sizes, from 1K to 10m rows, with thin, medium, and wide column data, and varying volumes of data returned, which I will blog about when I have an opportunity (next week, sometime). If you'd like to use the data to populate 64-bit personal geodatabases, then we'd have a way to know whether the speed is reasonable, and how much slower "slower" is. Compiling the data and script took 8-12 hours, but porting the data from FGDB to PostgreSQL took 5 minutes of effort, and tweaking the script to accept a different input folder took another five (IIRC, benchmark runtime against FGDB was 8-12 hours, but only half that for PG). I'll publish the scripts and the results with my blog entry.

- V

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

TedKowal — Wed, 31 Jan 2018 17:37:18 GMT

This discussion is not about speed but of compatibility with products which use the gis data outside of ESRI. Even though, slower, in the end due to the work effort - you reach your destination much faster. No I do not have data sets that large (as you indicated) that I use day to day, however I have worked with sets from the state in the neighborhood 4 Million records. Yes the MS database is slow even the 64 bit drivers, however the start to finish of transferring the data outside of ESRI to other products did not appreciable change. The work effort in translating the data from the file database to a compatible one far far far far exceeded the slowness of Access. In addition using MS Access did not introduce other unforeseen errors -- with access no additional programming was necessary to accomplish end goals.

Remember the story about the Hare and Tortoise!

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

DavidWheelock — Wed, 07 Feb 2018 22:45:45 GMT

Great use case Ted. I like your evidence showing cases PGDB performance exceeds FGDB in the final analysis!

Re: Enable ArcGIS Pro to access ESRI Personal Geodatabases

DavidWheelock — Thu, 08 Feb 2018 19:14:50 GMT

Well put.