Recently the company that I work for has upgraded my PC from Windows XP to Windows 7. On my new PC they've also update my version of MS Access from 2003 to 2010 and they also gave me a new domain account.
After setting up all of my previous ODBC drivers from my old PC onto my new PC as System DSNs (just as they had been previously) I tried to open one of my old Access databases and I could open any of my data tables or queries that access SQL Server data through linked, ODBC tables.
I tried opening various tables, from various databases and none would open. I would continuesly get the following error stating:
ODBC--Connection to 'MyDatabase' failed.
WTo troubleshoot the problem I decided to see what would happen if I linked to a different table. After getting to the screen where I am suppose to select my ODBC connection I noticed that none of my System DSNs exist on my PC were showing up in Access. Below is a screen shot of my ODBC setup. The top screen is the ODBC connections that are available in Access. The bottom screen is my ODBC management screen for Windows, showing that the ODBC DSNs exist. Why can't of find my System DSNs in Access?
As a quick note, I did setup one of these connections as a User DSN and my Access database started working. In the short term, I can setup a few User DSNs, but in the long term I will want my connections to be defined as System DSNs.
Since I can access my data through a User DSN definition, I doubt that this is an issue with the fact that my PC is a 64 bit machine and that there is some issue with a driver.
Also, I was told that I am an administrator on my PC and I've even been able to cut off all restrictions on my PC (i.e. I always cut off the UAC) so I doubt this is a permissions issue. However, I imagine that could be the case. What can I do to diagnose this problem and, hopefully, fix it?