Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm getting the following error when I try to open a form with Microsoft Access 2002 as a front end to an SQL Server 2000 Database.

"ODBC --connection to 'data source name' failed."

I made a new data source with all the correct information using the Windows ODBC Data source administrator because I know I can connect to the server fine through SQL Server Management Studio and I suppose the problem is with the data source. But I can't figure out how to indicate to Access that I want to use this new data source. I can't find any documentation about this either, so any help would be appreciated, including an explanation of what these data source really are...

share|improve this question
    
Have you tested your ODBC settings from something other than Access? –  user3463 Aug 24 '11 at 0:52
    
Yes, through SQL Server Management Studio –  Shawn Aug 24 '11 at 0:59
    
It's possible due to compatibility issues that Access won't recognise the existing data sources. Your proposed solution is the best bet, I think. –  user3463 Aug 24 '11 at 1:21
    
I'll wait it out a bit anyways –  Shawn Aug 24 '11 at 4:48
add comment

1 Answer

Here is the procedure I followed which allowed me to create a new data source and use it (I haven't yet found a way to use the pre-existing data sources, that's why I'm not accepting this answer):

Go to Tools->Database Utilities->Linked Table Manager
Select the "Always prompt for new location" checkbox
Select the concerned table
Click OK
Click "New..." to create a new data source
Select the newly created data source
BINGO
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.