Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
hic
Former Employee
Former Employee

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.

ODBC Basic.png

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:

QlikVIew Toolbar.png

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.

ODBC Admin.png

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.

Choosing data source.png

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.

ODBC 2 QlikView.png

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

HIC

36 Comments
linoyel
Specialist
Specialist

Thanks a lot for the info and the example - all was so clear and described in a simple and understandable way

0 Likes
682 Views
sunny_talwar

Got to learn something new. Thanks hic

0 Likes
682 Views
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Thanks for reviving this thread Sunny. Like all of HICs posts, it's worth pointing these tips out to the newcomers.

My homage to the Master

ODBC Confusion - YouTube

682 Views
Not applicable

Sorry, maybe define "ODBC" someplace for the newbies?

0 Likes
682 Views
Anonymous
Not applicable

Top demais .. funcionou pra mim ! Estava puxando a configuração do ODBC de 32 bits, meu banco Oracle só tem driver de 64 bits. Acessei Qlick (64 bits) e conseguir conectar ao meu banco. Parabéns pelo post muito bem explicativo.

0 Likes
682 Views
sannidhikumar
Creator
Creator

Hi ,

how to connect the MDB file in Qlik sense can any one define me ?

 

thanks 

0 Likes
677 Views