This is a problem I ran into recently. I have a 64-bit operating system.
I’m running Excel 2010, which is 32-bit. There is also a 64-bit version. (You can get this information by going to File > Help.)
I went to Programs > Administrative Tools > Data Sources (ODBC). I set up a System DSN. I tested it and it works fine. Hurray!
Then I opened Excel 2010 and went to Data > From Other Sources > From Microsoft Query. The DSN isn’t there. What happened?
I scratched my head over this for a while, and then emailed my team. One of my senior DBAs had run into this before and pointed me to this Microsoft KB article: http://support.microsoft.com/kb/942976. On a 64-bit computer, the DSN will be set up as 64-bit. However, a 32-bit application can’t see a 64-bit DSN. Excel 2010 is a 32-bit application.
To solve this problem, I needed to set up a 32-bit DSN. I did that by going running the 32-bit ODBC Data Source Administrator tool from C:WindowsSysWOW64odbcad32.exe. I created the same DSN, with _32Bit in the name.
I went back to Excel > Data > From Other Sources > From Microsoft Query. My 32-bit DSN is in the list.
What I learned is that 64-bit operating systems contain both 64-bit and 32-bit ODBC administration tools. You must use the appropriate tool for the application you are working with.
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
