Bug report #8752

Unable to open ESRI personal geodatabase in 2.0

Added by Russell Collier about 11 years ago. Updated about 8 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/OGR
Affected QGIS version:2.0.1 Regression?:No
Operating System:OS X Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:17462

Description

I upgraded to the new release and found I can longer open ESRI personal (not file) geodatabases when I choose to Add Vectors. The choice is no longer available from the file browser's File Types list. This function worked in my earlier installation - 1.8 Lisboa. I am running QGIS 2.0 on Mac OSX 10.8 Mtn Lion on a MacBook Pro (Retina).

Has this functionality been moved somewhere else, or is there something new I must install in order for this to work? I installed GDAL complete and NUMPY, also SCIPY, from KyngChaos.com downloads. Perhaps personal geodatabase import is simply not included in this release? If so, any ideas when it will be supported?

I have inherited many (many) Gb of critical data stored in ESRI personal geodatabases. I have a project colleague who requires access to all the data in this dataset. If the MDB access is no longer included, I need to know so I can make alternate arrangements to get the data into a format he can deal with.

Many thanks,
Russell in Canada

tnt20.mdb - Personal Geodatabase Sample (2.64 MB) Antonio Locandro, 2013-10-23 12:15 PM

Related issues

Duplicated by QGIS Application - Bug report #12054: ODBC and geodatabase access fails on windows 7 64 bits Closed 2015-01-26

History

#1 Updated by Giovanni Manghi about 11 years ago

  • Status changed from Open to Feedback
  • Target version set to Future Release - High Priority

The support for such formats comes within gdal/ogr. Did you check here

http://www.kyngchaos.com/software/frameworks#gdal

if you some plugin that you don't have installed and that you had while using qgis 1.8?

#2 Updated by Russell Collier about 11 years ago

Okay, I chatted for a bit with William at KyngChaos, and confirmed the Mac OSX version will not open ESRI Personal Geodatabases, and there really isn't much I can do about it. He is working on a plugin that may help.

Additionally, I tested the 32-bit Windows versus 64-bit Windows versions and found that the 64-bit won't open the PGEO datasets either. Interestingly, the 32-bit version of QGIS WILL open the datasets very easily.

The solution seems to be to install 32-bit Windows QGIS somewhere and deal with my personal geodatabases as needed there, and then save any data I require into a more neutral format, such as shapefiles. There is no reader for personal geodatabases in the Mac OS X version of QGIS, and nor will there be in the near future, and so I recommend we just turn out the light on this particular question, close the door, and tiptoe quietly away.

#3 Updated by Giovanni Manghi about 11 years ago

  • Status changed from Feedback to Closed
  • Resolution set to wontfix

I'm sorry... proprietary formats are always a pain in the aRs.

#4 Updated by Nathan Woodrow about 11 years ago

What was the reason that nothing can be done? Seems strange that we could with the old version but not the new one.

#5 Updated by Larry Shaffer about 11 years ago

  • Status changed from Closed to Reopened
  • Resolution deleted (wontfix)

Hi,

I actually just got MDB access working today for homebrew's GDAL. For me that equated to drag-dropping a .mdb file on QGIS and being presented with layers to load. Will be submitting a pull request soon to homebrew. Basically, the supporting MDB Tools library is obsolete. So, the PGeo and Geomedia OGR drivers were failing miserably. I got the MDB Tools setup to function, but fails to properly read layers via GDAL (inside or outside of QGIS).

Instead, I hacked the GDAL configure script to get the MDB driver to work with a default Java 1.6.0 install. Direct layer loading speed from the .mdb is not awesome, but it functions just fine: loading geometries and attribute tables appear OK.

Only tested on Mac OS X 10.7.5 so far, with current master.

#6 Updated by Jürgen Fischer about 11 years ago

  • Resolution set to up/downstream
  • Status changed from Reopened to Closed
  • Operating System set to OS X

Hm, we should decide what to file here. For me it's out of scope as it's not a QGIS problem, but a GDAL packaging problem on OS X.

#7 Updated by Larry Shaffer about 11 years ago

Jürgen Fischer wrote:

Hm, we should decide what to file here. For me it's out of scope as it's not a QGIS problem, but a GDAL packaging problem on OS X.

Agreed. It is a Mac GDAL-specific issue, and only related to QGIS packaging. I have a ticket submitted for a fix for GDAL.

In the meantime, if Mac users want to try using GDAL to convert .mdb files over to a QGIS-useable OGR format (until a fix is available upstream) they can do one of the following:

  • Ensure a Java JDK is installed, then...
  • Then to have a functional MDB driver, install supporting .jar files in /Library/Java/Extensions/ See OGR MDB driver page

#8 Updated by Antonio Locandro about 11 years ago

  • Status changed from Closed to Reopened

I can't open it using 64 bit windows install, any options here since apparently the 32 bit version can do it?

#9 Updated by Antonio Locandro about 11 years ago

This is a sample Personal Geodatabase which I cant open in QGIS 2.0 64 bit

#10 Updated by Jürgen Fischer about 11 years ago

  • Status changed from Reopened to Closed

Antonio Locandro wrote:

This is a sample Personal Geodatabase which I cant open in QGIS 2.0 64 bit

You need a 64bit ODBC driver for Access. See http://www.microsoft.com/en-us/download/details.aspx?id=13255 (Access 2010)

But GDAL expects the Access ODBC driver to be called Microsoft Access Driver (*.mdb), although unfortunately the Access 2010 ODBC driver is registered as Microsoft Access Driver (*.mdb, *.accdb). The PGeo driver starts to work if you rename the key HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBCINST.INI\\Microsoft Access Driver (*.mdb, *.accdb) to HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBCINST.INI\\Microsoft Access Driver (*.mdb) in regedit.

Maybe there is a earlier 64bit ODBC driver for Access that still registers itself under the expected name.

#11 Updated by Jürgen Fischer about 11 years ago

Jürgen Fischer wrote:

But GDAL expects the Access ODBC driver to be called Microsoft Access Driver (*.mdb),

That's just the default - it can be changed by setting the option PGEO_DRIVER_TEMPLATE (see http://gdal.org/ogr/drv_pgeo.html), but there is currently no way to set it in QGIS.

#12 Updated by Bernd Vogelgesang about 11 years ago

  • Status changed from Closed to Reopened

Please, can someone figure out a solution for this mess ?
I had OSGEO4W64 installed on Win7 Pro 64bit, no chance to open a .mdb. Followed Jürgens solution, but installing the 64bit Access drivers did not work, cause i had a 32bit Office 2010 installed. So had to uninstall that, installed the driver and reinstalled a 64bit Office 2010. Then changed the key name with regedit as mentioned.
I successfully open the test .mdb provided by Antonio, but the .mdb i have to work with (51mb) crashes QGIS when trying to open it.

So now I installed the 32bit-version in parallel, cause it is said that it still eats mdb. Here now same result: Test .mdb works, my .mdb crashes the application.
Opening was possible with 1.8/1.9 32bit before walking into the 64bit trap.
Even if its not a QGIS bug, i would appreciate some ideas how to get out of this trouble and how to prevent other users to run into this dead end.
I know, ...... proprietary sh.. , but, thats real life.

#13 Updated by Jürgen Fischer about 11 years ago

Bernd Vogelgesang wrote:

Even if its not a QGIS bug, i would appreciate some ideas how to get out of this trouble and how to prevent other users to run into this dead end.
I know, ...... proprietary sh.. , but, thats real life.

Steps to reproduce (ie. a file that crashes QGIS/GDAL)?

#14 Updated by Larry Shaffer about 11 years ago

Bernd Vogelgesang wrote:

Please, can someone figure out a solution for this mess ?
...
I know, ...... proprietary sh.. , but, thats real life.

Yes, especially in the US, where there are quite a few gov agencies distributing .mdb files still.

Similar to the compiling of GDAL with the Java-based MDB driver, I have successfully done the same for Mac, but as a shared plugin. Please see this gis.stackexchange.com post.

I have not tried to do this on Windows, and there appears to be no Java install in the current OSGeo4W 64-bit setup. Although, a system Java install could be used. You will need both the JRE and JDK of a system install. In other words, you can try to compile the shared driver yourself, using the current OSGeo4W install.

#15 Updated by Antonio Locandro about 11 years ago

Tested again, it seems the issue is related to the size of the MDB, I have progressively have tested the Test case which is 2.7 MB, I successfully opened 18 MB, 22 MB, 30 MB; then tried to open a 394 MB file and it crashes, I don't have a similar 50 MB mdb, but I suspect there is some threshold where it begins to crash

Note, after crash I opened the file again and let it load without touching anything. after about 5 minutes it loads. It crashes if you click on the canvas before it has read the files is my guess

#16 Updated by Giovanni Manghi about 11 years ago

Antonio Locandro wrote:

Tested again, it seems the issue is related to the size of the MDB, I have progressively

Test case is 2.7 MB, I successfully opened 18 MB, 22 MB, 30 MB

Then tried to open a 394 MB file and it crashes, I don't have a similar 50 MB mdb, but I suspect there is some threshold where it begins to crash

in a training course I successfully opened both personal and file GDB on both qgis 32 and 64 bit/osgeo4w. Small ones and also large ones (~500mb).

#17 Updated by Antonio Locandro about 11 years ago

Would there be an easier fix in 64 bit than the hackish proposed solution? Especially since the 32 bit version on the same machine works fine

#18 Updated by Jürgen Fischer about 11 years ago

Antonio Locandro wrote:

Would there be an easier fix in 64 bit than the hackish proposed solution? Especially since the 32 bit version on the same machine works fine

Find an older 64bit Access ODBC driver. I don't know if such a thing exists. I also don't know if newer 32bit ODBC drivers would also register under the new name and even produce the same problems on 32bit with PGeo files. So updating the OGR provider allow configuration of PGEO_DRIVER_TEMPLATE to avoid the registry hack might not help much either.

#19 Updated by Jürgen Fischer about 11 years ago

Does ESRI require ODBC drivers to access personal geodatabases? If so, how do they manage on 64bit? SCNR

#20 Updated by Neil Benny about 11 years ago

Can anyone update me about this? - has there been any breakthrough? Is it scheduled as a bug-fix on the new version?

#21 Updated by Jürgen Fischer about 11 years ago

Neil Benny wrote:

Can anyone update me about this? - has there been any breakthrough? Is it scheduled as a bug-fix on the new version?

Updates should be linked here.

#22 Updated by Antonio Locandro over 10 years ago

For some reason QGIS keeps crashing on me for Personal Geodatabases in 32 bit, any ideas? I have tried QGIS with noplugins and still crashes

#23 Updated by Jürgen Fischer over 10 years ago

  • Category set to Data Provider/OGR

#24 Updated by Nyall Dawson about 8 years ago

  • Status changed from Reopened to Closed

Upstream issue, not a qgis issue.

Also available in: Atom PDF