The Great ODBC Confusion

[hic]

Before the ODBC interface to databases was developed in the late 80:s and early 90:s, it was difficult to connect to a database and import data. But thanks to Microsoft and some other DB vendors, we got an open interface with which we still today can load data from almost any database.

But, some aspects of the Windows ODBC implementation are confusing…

When ODBC was developed, computers were running DOS or Windows 3.1, i.e. 16-bit programs, and as a consequence, ODBC was also 16-bit. Then came 32-bit programs and it got messy: You could not use the 16-bit ODBC with your 32-bit programs – you had to use the 32-bit ODBC. But at least there were two icons for the two different ODBC:s in the control panel, so it was clear what you needed to do to configure the right driver.

Today, with 64-bit operating systems, we have a similar situation: There are both 32-bit and 64-bit programs. But it has become even more confusing, because there is only one ODBC icon in the control panel. And many users do not know that this is just for the 64-bit ODBC.

Facts:

• A program always needs the correct ODBC driver: A 64-bit driver for 64-bit programs, and a 32-bit driver for 32-bit programs.
• The 64-bit drivers are configured using C:\Windows\System32\odbcad32.exe
  (Can be started from the ControlPanel ˃ Adminstrative Tools > Data Sources (ODBC).)
• The 32-bit drivers are configured using C:\Windows\SysWoW64\odbcad32.exe
  (Cannot be started from the control panel.)

And, no, there are no typos in the paths and the file names. They are really named like that. Trust me. The 32-bit administrator really is found in SysWoW64, and the 64-bit is found in System32. Microsoft cannot have had any usability tests here...

To simplify things, QlikView has menu items for both ODBC administrators in the script editor:

With these, you can open the correct ODBC Administrator easily. But note – the two administrators cannot run at the same time. You need to close the open one before you can open the other.

Further, when you create your data source, you can choose between creating a User DSN or a System DSN. The latter can be accessed by any user, while the former can be accessed only by you.

But unfortunately, that is not the only difference between the two. There is a second, confusing difference: The list of User Data Sources is a mixture between 32-bit and 64-bit User DSN:s, so when you create the connect string, you will also see unusable data sources (see below). If you choose an unusable data source, you will get an error message that talks about an “architecture mismatch”. To avoid mixed lists, you should never create any User DSN:s. Always create System DSN:s.

Finally, a couple of words on how it is that QlikView can use both 32- and 64-bit ODBC drivers: The QV.exe itself never connects to ODBC. Instead it launches a separate process. Depending on whether CONNECT32 or CONNECT64 is used, QVConnect32.exe or QVConnect64.exe is launched, which connects to ODBC and streams the data in QVX format to the QV.exe.

With this solution it is possible to use 32-bit ODBC drivers together with your 64-bit QlikView.

HIC