View RSS Feed

Development Team Blog

I Cannot Connect To An ODBC Database

Rating: 5 votes, 5.00 average.
I have setup a USER DSN in the ODBC Manager (Data Sources) which points to an Microsoft Access demodata database. Now I want to connect to this database with Data Access's Visual Report Writer and with Visual DataFlex Studio. In Visual Report Writer I simply cannot get past the selection of the DSN and in Visual DataFlex Studio I get a "Unable to Login to Server!" message. What is wrong? I thought DataFlex could connect to ODBC databases, isn't that true? If I use Microsoft Excel from Microsoft Office 2010 I have no problem, I can connect to the database, open tables and get results.

Does the above sound familiar to you? If yes than you are most likely using a 64 bit version of the Windows operating system, for example 64 bit Windows 7 . You should know that you can perfectly fine run 32 bit and 64 bit applications in a Windows 64 bit operating system. With this it is important is that for a 32 bit application you need to use 32 bit modules and with a 64 bit application you need to use 64 bit modules (DLL's and COM). In my Windows installation there are two drivers (a 32 bit and a 64 bit) for the Microsoft Access database. Where they come from is outside the scope of this BLOG. Having both sounds perfect as it helps the 32 bit and the 64 bit application developers.

Now, what has this to do with the problem? When you setup your DSN you use the ODBC Manager (menu choice is Data Sources) tool. This tool comes in a 32 bit and a 64 bit version. For both versions the name of the manager is ODBCad32.exe. To be able to have both on your machine the 64 bit version is stored in c:\windows\system32 folder and the 32 bit version is stored in c:\windows\syswow64 folder. By default the "Data Sources (ODBC)" menu option links to the 64 bit version in the system32 folder.

With both versions you can view USER DSN's for 32 bit and 64 bit drivers. The USER DSN list is not filtered because the information comes from a INI file which does not offer the filtering option. You cannot define or configure a 32 bit USER DSN with the 64 bit version and vice-versa. If you were creating a SYSTEM DSN the tool can filter on 32 or 64 bit. The 64 bit version will show 64 bit DSN configurations while the 32 bit version shows the 32 bit DSN configurations.

If you setup a USER DSN with the 64 bit version the ODBC Manager tool will use the 64 bit ODBC drivers and your USER DSN cannot be used from Visual DataFlex or Visual Report Writer.

Having the "wrong" driver behind the DSN makes it impossible to configure or remove the DSN with the started ODBC Manager. If you start the (default) 64 bit version of the ODBC manager and you try to remove the USER DSN for a 32 bit driver you will get the errors:
  • The setup routines for the Microsoft Access Driver (*.mdb) ODBC driver could not be found. Please reinstall the driver.
  • The specified DSN contains an architecture mismatch between the Driver and Application

Some advises if you create a USER DSN:
  • Add the word 32(bit) in the name of the DSN
  • Make a link to the 32 bit ODBC Manager, for example pin it to your task-bar

Useful resource links are: