Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .