ODBC and geodatabase access fails on windows 7 64 bits

[qgib]

Author Name: Regis Haubourg (@haubourg)
Original Redmine Issue: 12054
Affected QGIS version: 2.6.0
Redmine category:data_provider

---

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":https://robertoschiabel.wordpress.com/2008/02/28/windows-x64-32bit-odbc-vs-64bit-odbc/

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

---

Related issue(s): #17462 (duplicates), #21945 (duplicates), #26125 (relates)
Redmine related issue(s): 8752, 13927, 18234

---

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

see also "GDAL !#5594":http://trac.osgeo.org/gdal/ticket/5594

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

I can open the mdb attached to #17462 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

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

Jürgen Fischer wrote:

I can open the mdb attached to #17462 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..

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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...

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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":http://www.microsoft.com/en-us/download/details.aspx?id=13255 installed?

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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..

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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?

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

Hi,
here is a "tip":http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/ to install 64 bit access driver if Office 32 bit is installed
That works ! Hope that won't break office too much..

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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..

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

Regis Haubourg wrote:

Hi,
here is a "tip":http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/ 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 !

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Addressed in GDAL trunk

---

• status_id was changed from Open to Closed
• resolution was changed from to up/downstream

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• relates was configured as Esri Personal GeoDatabase (*.mdb file) #26125