MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1470

Great article; I've been up and down this road many times in the past year and can vouch that you are hitting the connection using DSN right on the head!

It'd be worth while to note that if for some reason the reader can't find "Data Sources (ODBC)" in their Administrative Tools; to also check in your Control Panel (This is very typical in Pre-XP operating systems).

Also, if you still can't find it don't fret, you can usually go into your Windows Components (accessed through the 'Add/Remove Programs' component), and choose to add it in to your Control Panel or Administrative Tools. The option to select it might be a few levels in to the components, so click on 'Details' if the option is there. Have your Windows OS disc ready; sometimes needed to get the files installed. 

Still, can't find it? Well, unfortunately you can't just download the ODBC control by itself, you will have to download Microsoft Data Access Control (MDAC). Be careful to choose the correct version, some versions are not compatible with older Access versions...such as Access 97 doesn't work reliably with MDAC 2.7, and is suggested to use MDAC 2.6 instead. You can get good troubleshooting through Microsoft's Support Knowledge Base (KB)...Do a search on MDAC or ODBC...here's a link to an informative KB for MDAC 2.7 (Article ID # 313008). It's a great starting point, that includes link for setup troubleshooting and getting to older MDAC versions. http://support.microsoft.com/kb/313008/en-us

Keep up the great series of tips for this somewhat confusing area!

jrea8830,

Thank you for the insight and feedback.

You are correct, this is another good tip from Tim Ford.

Thank you,
The MSSQLTips.com Team

Thank you for such a nice tip. I would rather say an article. Now the issue: We are running an SQL Server in a large organization in a small section. One User account (Lets Call is USER9999) was created on the server and the front-end application developed in MS Access 2003 is deployed on a Citrix Server. Users can log on to the Citrix Server and access this application through their Citrix Server account managed by a domain controller. The application residing on the Citrix Server connects to the SQL Serevr using the above mentioned account USER9999 which we created on the SQL server and is NOT a trusted account. Time and again users complain of not being able to execute the ODBC of this front-end application in MS Access 2003 since the machine on which this single application is installed tries to access the SQL Server using "Trusted  User Account". When this happens, we have to connect to the application and refresh the ODBC and Tables with "Linked Table Manager". In all these instances the culprit was the "bad" ODBC which was trying to access the SQL Server through Trusted Domain User Account NOT the SQL Server Account (as this option was checked all the time). My question is: Is there any way that we can create an ODBC and "engrave" in it to use SQL Server Account and NOT the Trusted account? The SQL Sever is the member of the domain and running a small database with few users.

I will appreciate any comment on this issue,

 Thanks

Very interesting behavior going on in your environment.  Can you confirm that you're using a System DSN and that it resides on the Citrix server where the Access .mdb resides?

- Tim

Did you ever find an answer to your question? We are running into the same issue. Even though we chose SQL Server authentication during setup, it defaults to the trusted connection and we have to uncheck and re-enter the user name and password.