Opened 10 years ago

Closed 5 years ago

#5594 closed defect (wontfix)

Problem reading MDBs (64-bit)

Reported by: jmitchell Owned by: warmerdam
Priority: normal Milestone: closed_because_of_github_migration
Component: default Version: unspecified
Severity: normal Keywords: mdb
Cc:

Description

I have been trying to get both ordinary Access Database MDBs and also Personal Geodatabase MDBs to load in 64-bit QGIS 2.4 on Windows (installed via the 64-bit OSGeo4W installer). I have also tried with no success to read MDBs from the command line using ogrinfo.

I think that the problem is that GDAL/OGR expects the MS Access ODBC Driver to be named "Microsoft Access Driver (*.mdb)" This is ok when running 32-bit versions because Windows includes a default 32-bit driver with this name out of the box.

However there is no equivalent 64-bit driver included with Windows. To get a 64-bit Access ODBC Driver you have to install a 64-bit version of Access or Access Runtime and all versions of the 64-bit driver are named "Microsoft Access Driver (*.mdb, *.accdb)"

The only way I have been able to get MDBs to load in 64-bit QGIS is by renaming the Registry 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) using regedit.

While this hack enables MDBs to load in QGIS (by drag and drop) I don't know whether renaming this registry key may cause other unforeseen issues and also it still doesn’t enable MDBs to be read correctly with ogrinfo on the command line.

I have also tried setting the PGEO_DRIVER_TEMPLATE Config Option to "DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s" but for some reason this setting seems to be ignored when using the OSGeo4W 64-bit install.

Change History (12)

comment:1 by rhaubourg, 9 years ago

Confirmed here, related to http://hub.qgis.org/issues/12054 Cheers Régis

comment:2 by rhaubourg, 9 years ago

Suggested workaround with registry does not work here. Registry tree seems different here, I need to explore Wow6432Node sub folder to find the same keys.

comment:3 by jef, 9 years ago

The ODBC driver takes precencence over the PGEO driver. Using @OGR_SKIP=ODBC@ helps here to make OGR use the PGEO driver and make @PGEO_DRIVER_TEMPLATE@ stick.

Version 0, edited 9 years ago by jef (next)

comment:4 by Even Rouault, 9 years ago

@jef Are you 100% positive that it fixes the issue ? Since GDAL 1.10, the ODBC driver indeed tries to open .mdb files if they are non-spatial. "DRIVER=Microsoft Access Driver (*.mdb);DBQ=%s" is currently hard-coded in the ODBC driver (without using PGEO_DRIVER_TEMPLATE), so it could explain that non spatial .mdb files cannot be opened (with an error message 'Unable to initialize ODBC connection to DSN for XXXX'), but this shouldn't theoretically prevent the PGeo driver from being called afterwards.

comment:5 by rhaubourg, 9 years ago

Hi, proposed solution does not work here for QGIS 2.6.1 et today's master..

comment:6 by jef, 9 years ago

Yes.

Without OGR_SKIP=ODBC and PGEO_DRIVER_PATH and with either one applied the error messages in QGIS are:

Datenquelle ungültig (Unable to initialize ODBC connection to DSN for DRIVER=Microsoft Access Driver (*.mdb);DBQ=D:\TEMP\test\8\8752\tnt20.mdb,
			[Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben)

Only with both it works.

comment:7 by Even Rouault, 9 years ago

I've committed in trunk r28368 "ODBC, PGeo: try alternate DSN template for 64bit ODBC; make ODBC driver honour PGEO_DRIVER_TEMPLATE config. option (and also MDB_DRIVER_TEMPLATE in case PGEO_DRIVER_TEMPLATE isn't defined) (#5594)"

It's only compiled tested, so likely not working without adjustments. I let you test and tweak this...

comment:8 by rhaubourg, 9 years ago

For users stucked with office 32 bit, Windows forbids installation of access 64 bit client, unless using passive mode. See http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/. This with OGR_SKIP=ODBC and PGEO_DRIVER_PATH set in QGIS.bat solves opening of geodatabases on Windows 64 bit. Anyway, opening classical mdb does not work. Any idea? Extend PGEO driver to open native DB ?

in reply to:  8 comment:9 by Even Rouault, 9 years ago

Replying to rhaubourg:

For users stucked with office 32 bit, Windows forbids installation of access 64 bit client, unless using passive mode. See http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/. This with OGR_SKIP=ODBC and PGEO_DRIVER_PATH set in QGIS.bat solves opening of geodatabases on Windows 64 bit. Anyway, opening classical mdb does not work. Any idea? Extend PGEO driver to open native DB ?

Régis, my above fix in trunk should solve it hopefully. Just needs someone to test it and confirm. The ODBC driver has support for non-PGEO databases (so it OGR_SKIP=ODBC must *not* be set with the fix)

comment:10 by rhaubourg, 9 years ago

Thanks Even. I have no platform to compile qgis with gdal trunk by now. I will have to follow osgeo4w versions to test it. Cheers Régis

comment:11 by rhaubourg, 9 years ago

In fact, installing access odbc 64 bit drivers breaks Office install. No solution for Office 32 bits users (most of us since MS does not recommend Office 64 bit).

comment:12 by Even Rouault, 5 years ago

Milestone: closed_because_of_github_migration
Resolution: wontfix
Status: newclosed

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to https://github.com/OSGeo/gdal/issues if it is not already reported there.

Note: See TracTickets for help on using tickets.