ODBC workaround (Access to SQL Server) for 64-bit Windows 7
I've been avoiding 64-bit Windows due to various incompatibility rumors, but this case takes the cake, as it is entirely Microsoft's fault. My work place uses a variety of shared Access databases, located on a network drive, that connect via ODBC System DSN's to a SQL Server 2008.
Even though all DSN's appeared to be configured correctly on my colleague's brand new (64-bit) Windows 7 machine, and the ODBC connections passed their test, the actual database declined to connect to the server. Thanks to various discussion groups, we finally figured out that the graphical user interface accessible from the Administrative Tools applet in Control Panel actually brings up a 64-bit ODBC application, whereas we (for backwards compatibility) needed the strangely hidden 32-bit System DSN window. To run it, we had to browse to this path:
C:\Windows\SysWOW64\odbcad32.exe
Clicking on odbcad32.exe runs the 32-bit version of the ODBC connection setter upper. There, we re-created all the System DSN's, and finally the Access databases were happy.
By default, the Windows GUI is presenting a 64-bit ODBC connection setter upper (which I believe is in the C:\Windows\system32 path somewhere. Going manually to the first path and running the application, then adding the ODBC connections, makes it work.
In the meantime, 3 days of work were lost to this problem.
Even though all DSN's appeared to be configured correctly on my colleague's brand new (64-bit) Windows 7 machine, and the ODBC connections passed their test, the actual database declined to connect to the server. Thanks to various discussion groups, we finally figured out that the graphical user interface accessible from the Administrative Tools applet in Control Panel actually brings up a 64-bit ODBC application, whereas we (for backwards compatibility) needed the strangely hidden 32-bit System DSN window. To run it, we had to browse to this path:
C:\Windows\SysWOW64\odbcad32.exe
Clicking on odbcad32.exe runs the 32-bit version of the ODBC connection setter upper. There, we re-created all the System DSN's, and finally the Access databases were happy.
By default, the Windows GUI is presenting a 64-bit ODBC connection setter upper (which I believe is in the C:\Windows\system32 path somewhere. Going manually to the first path and running the application, then adding the ODBC connections, makes it work.
In the meantime, 3 days of work were lost to this problem.
Labels: DSN, ODBC, SQL Server, System administration, Windows
posted by Harbor Sparrow at
8:29 AM
4 Comments:
I had the same problem. One SQL connection worked, one did not, removed them all, and added them and worked fine. Despite tests concluded succesfully.
By
Anonymous, at 12:03 AM
This comment has been removed by a blog administrator.
By
Anonymous, at 7:01 PM
3 years later. thank you for the solution. Only 2 days lost. Argg.
By
gillettd, at 3:54 PM
Thank you very much, mine worked with the 64 bit administrator but only temporaly, the ODBC link to our SQL server was removed misteriously by the system...
By
Ringmaster, at 2:27 AM
Post a Comment
<< Home