up vote 0 down vote favorite
share [g+] share [fb]

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...

link|improve this question
55% accept rate
Have you tested your ODBC settings from something other than Access? – Randolph West 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. – Randolph West Aug 24 '11 at 1:21
I'll wait it out a bit anyways – Shawn Aug 24 '11 at 4:48
feedback

1 Answer

up vote 0 down vote

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
link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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