Opened 5 years ago

Closed 2 months 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 Changed 4 years ago by rhaubourg

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

comment:2 Changed 4 years ago by rhaubourg

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 Changed 4 years ago by jef

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.

Last edited 3 years ago by jef (previous) (diff)

comment:4 Changed 4 years ago by Even Rouault

@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 Changed 4 years ago by rhaubourg

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

comment:6 Changed 4 years ago by jef

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 Changed 4 years ago by Even Rouault

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 Changed 4 years ago by 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 ?

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

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 Changed 4 years ago by rhaubourg

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 Changed 4 years ago by rhaubourg

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 Changed 2 months ago by Even Rouault

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.