ODBC connection issues in Windows 7

OK, so you have a database (eg SQL server) with an MS Access or similar front end. You have been using ODBC to connect to this database without issues for literally years with Windows XP clients. Recently you upgraded to some Windows 7 machines, and now the connection is not working -- what's gone wrong?

Well, the short answer is ---

For Windows 7, Connect using a User DSN rather than a System DSN in the "Data Sources (ODBC)" control panel.

I am not sure why the System DSN's are broken in Windows 7, but it seems they are.

Although it works in Windows 7 using a User DSN, this is not as good in some ways. If the machine is used in a hot-desk environment, then you will have to set up the ODBC driver for each User, rather than once for the System. For hand-me-down situations, it's one more thing to remember to setup. I have not tried a Terminal Services machine lately, but it's likely to be a pain in the butt on these as well.

If this post helped you, please leave a comment!

EDIT : 2012-05-31

I have had reason in my professional life to revisit this annoying issue, and have some more information to add.  It turns out that this is a 32/64 bit issue, and there are TWO versions of the ODBC admin control panel, based on 32 or 64 bit. It is explained in more detail in this knowledgebase article, which I recommend you read in full

http://support.microsoft.com/kb/942976

What I love about this situation is the classic brain melter, and I quote ....

• The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
• The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

I had to read that one about 3 times before it sunk in.  The 32 bit version is in the SysWow64 folder, and the 64 bit is in the System32 folder. Hmmmm, logical.

Anyway, this all explains the behaviour and fix above - for USER DSN's, either version handle 32/64 bit, but for the MACHINE DSN's you have to use the right one, and the 32 bit is more common for older drivers and it's not the default tool in Win7 64 bit installs. I think I got that right!!