Running a program that uses ODBC on a 64-bit PC with the database on a 32-bit PC/Server

With the push to upgrade to 64-bit the problems of using data across 32-bit and 64-bit has been...well, a headache and a pain in the butt!!  Luckily, most of these problems can be dealt with rather easily once you know how, but finding that how can be tricky.

Here is the scenerio:
We have a program running a SQL backend on a 32-bit Server 2003 machine. The front end is written in Access 2003. On Windows XP and Windows Vista 32-bit this works fine. The client recently started a slow upgrade as funds allowed and purchased a Windows 7 64-bit machine. The install program ran fine on the machine (logged on as the local administrator, not as a domain user). However, when I opened the program, it would give me a "ODBC Error - Cannot connect to XXXXX" Where XXXXX is the name of the database.

Here is the Problem:
The install program created the ODBC. If I went in and looked at the System DSN tab in ODBC (Administrative Tools -> ODBC -> System DSN Tab) I would see the correct entry. I could edit it and everything would be correct. I could test the connection and it would run successfully. Puzzling! I even deleted the entry and created it by hand, again getting a successful test but when I opened the program I would get the dreaded ODBC Error.

Here is the Solution:
A little known fact on 64-bit machines is there are TWO, yes TWO ODBC calls. Of course, the default is to call the 64-bit program. While the System DSN will test correctly, the 32-bit program does not know how to call that particular ODBC connection. What you will need to do is to browse to C:\Windows\SysWOW64\odbcad32.exe and run this exe program. What comes up is the all familiar ODBC screen. Switch to the System DSN tab and create the ODBC as you normally would. Close and open the program and Ta-Da, now the 32-bit program can find the correct ODBC and it connects to the database.