Bug report #12054

ODBC and geodatabase access fails on windows 7 64 bits

Added by Regis Haubourg almost 5 years ago. Updated almost 4 years ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider
Affected QGIS version:2.6.0 Regression?:No
Operating System:Windows - x64 Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:20256

Description

Hi,
ODBC access and geodatabase access that is internally based on ODBC fails on windows 64 bits platforms,.
This is related to two differents implementations of ODBC manager, one for 32 bits, the other for 64 bits. I guess QGIS or GDAL uses always 32 bit executable.
See this article

This ticket is probably concerning GDAL upstream. Can anyone confirm? If true, I will file a ticket there.


Related issues

Related to QGIS Application - Bug report #18234: Esri Personal GeoDatabase (*.mdb file) Closed 2018-02-26
Duplicates QGIS Application - Bug report #8752: Unable to open ESRI personal geodatabase in 2.0 Closed 2013-10-02
Duplicated by QGIS Application - Bug report #13927: add layer vector mdb Closed 2015-12-01

History

#1 Updated by Jürgen Fischer almost 5 years ago

see also GDAL #5594

#2 Updated by Jürgen Fischer almost 5 years ago

I can open the mdb attached to #8752 using following in the qgis.bat:

set OGR_SKIP=ODBC
set PGEO_DRIVER_TEMPLATE=DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%%s

Apparently OGR's ODBC driver takes precedence over the PGEO driver and has the same problems as the PGEO driver w/o PGEO_DRIVER_TEMPLATE, which it obviously doesn't use.

edit: quotes removed

#3 Updated by Regis Haubourg almost 5 years ago

Jürgen Fischer wrote:

I can open the mdb attached to #8752 using following in the {{qgis.bat}}:

[...]

Apparently OGR's ODBC driver takes precedence over the PGEO driver and has the same problems as the PGEO driver w/o PGEO_DRIVER_TEMPLATE, which it obviously doesn't use.

That solution doesn't work here..

#4 Updated by Jürgen Fischer almost 5 years ago

Regis Haubourg wrote:

That solution doesn't work here..

Odd. I must have added the quotes after I tried. Without the quotes it works here...

#5 Updated by Regis Haubourg almost 5 years ago

Jürgen Fischer wrote:

Regis Haubourg wrote:

That solution doesn't work here..

Odd. I must have added the quotes after I tried. Without the quotes it works here...

With or without quotes, I still don't get it to work. I double checked that env var were correctly seen from qgis session.

#6 Updated by Jürgen Fischer almost 5 years ago

Regis Haubourg wrote:

With or without quotes, I still don't get it to work. I double checked that env var were correctly seen from qgis session.

Just to confirm: you have the 64bit Microsoft Access driver installed?

#7 Updated by Regis Haubourg almost 5 years ago

Ouch, you get it!
But no way to solve that issue - Redmond's fault : * It is not possible to install 64 bit access driver when Office is running 32 bit* (WTF?!).
My sys admin won't upgrade office to 64 bit, since Office 2010 is advertised NOT TO BE USED by Microsoft itself.. I couldn't have QGIS 32 bit use ODBC on a 64 bit windows platform..

So, I see few solutions:
- bet that office 2013 will upgrade to 64 bits
- fund OGR drivers that do not rely on Microsoft drivers, like xls drivers. Are you aware of such tools?

I'm a bit puzzled.. Interoperability looses a lot there..

#8 Updated by Jürgen Fischer almost 5 years ago

Regis Haubourg wrote:

Ouch, you get it!
But no way to solve that issue - Redmond's fault : * It is not possible to install 64 bit access driver when Office is running 32 bit* (WTF?!).
My sys admin won't upgrade office to 64 bit, since Office 2010 is advertised NOT TO BE USED by Microsoft itself.. I couldn't have QGIS 32 bit use ODBC on a 64 bit windows platform..

Whether your operating system is 32bit or 64bit doesn't really matter. You can use 32bit QGIS on both. But you need a 32bit ODBC driver for it (and that might still advertise itself without *.accdb and you don't need PGEO_DRIVER_TEMPLATE).

If you want 64bit QGIS you need a 64bit OS and a 64bit ODBC driver - and AFAIK there is no 64bit Access ODBC driver without *.accdb so you always need PGEO_DRIVER_TEMPLATE (and in turn OGR_SKIP) too.

As you don't need to configure a data source manually for GDAL/QGIS you don't need to be careful to use the odbcad32.exe from %WINDIR%\\SYSWOW64 on a 64bit systems to configure 32bit data sources either (but you can use it to verify which 32bit ODBC drivers you have).

So, I see few solutions:
- bet that office 2013 will upgrade to 64 bits
- fund OGR drivers that do not rely on Microsoft drivers, like xls drivers. Are you aware of such tools?

I'm a bit puzzled.. Interoperability looses a lot there..

On Unix GDAL uses unixodbc and mdbtools - I guess that could be stripped down to work on Windows. But I don't think it's worth the effort - even ESRI switched to something else, didn't they?

#9 Updated by Regis Haubourg almost 5 years ago

Hi,
here is a tip to install 64 bit access driver if Office 32 bit is installed
That works ! Hope that won't break office too much..

#10 Updated by Regis Haubourg almost 5 years ago

Shall I close the ticket ? Any work remaining on OGR side?

#11 Updated by Regis Haubourg almost 5 years ago

Jürgen Fischer wrote:

Regis Haubourg wrote:

Ouch, you get it!
But no way to solve that issue - Redmond's fault : * It is not possible to install 64 bit access driver when Office is running 32 bit* (WTF?!).
My sys admin won't upgrade office to 64 bit, since Office 2010 is advertised NOT TO BE USED by Microsoft itself.. I couldn't have QGIS 32 bit use ODBC on a 64 bit windows platform..

Whether your operating system is 32bit or 64bit doesn't really matter. You can use 32bit QGIS on both. But you need a 32bit ODBC driver for it (and that might still advertise itself without *.accdb and you don't need PGEO_DRIVER_TEMPLATE).

Does that mean it's not possible to open classical mdb files (non geodatabases)? I couldn't open any, when Geodb are allright

If you want 64bit QGIS you need a 64bit OS and a 64bit ODBC driver - and AFAIK there is no 64bit Access ODBC driver without *.accdb so you always need PGEO_DRIVER_TEMPLATE (and in turn OGR_SKIP) too.

As you don't need to configure a data source manually for GDAL/QGIS you don't need to be careful to use the odbcad32.exe from %WINDIR%\\SYSWOW64 on a 64bit systems to configure 32bit data sources either (but you can use it to verify which 32bit ODBC drivers you have).

So, I see few solutions:
- bet that office 2013 will upgrade to 64 bits
- fund OGR drivers that do not rely on Microsoft drivers, like xls drivers. Are you aware of such tools?

I'm a bit puzzled.. Interoperability looses a lot there..

On Unix GDAL uses unixodbc and mdbtools - I guess that could be stripped down to work on Windows. But I don't think it's worth the effort - even ESRI switched to something else, didn't they?

Well, users seem to keep working with it (not here, but we rely on them) because they have a Access relational db GUI , when other formats do not allow such things..

#12 Updated by Regis Haubourg almost 5 years ago

Regis Haubourg wrote:

Hi,
here is a tip to install 64 bit access driver if Office 32 bit is installed:
That works ! Hope that won't break office too much..

EDIT: This tips breaks office 32 bit access install. DO NOT FOLLOW THAT !

#13 Updated by Jürgen Fischer almost 5 years ago

  • Status changed from Open to Closed
  • Resolution set to up/downstream

Addressed in GDAL trunk

#14 Updated by Jürgen Fischer over 1 year ago

Also available in: Atom PDF