Solved

Access Error: ODBC Call Failed: Data source name was not found and no default drivers specified (#0)

Posted on 2008-10-06
7
1,189 Views
Last Modified: 2013-11-25
User is first going into Control Panel and creating a new System DSN connection to the SQL instance in question.  User has proper permissions on the SQL instance/database in question.  

He gets this message when he opens a new Access Database, right clicks, selects "Link Table" and selects ODBC as the file type, then selects the System DSN he set up.  When setting up the System DSN it said "Test Completed Successfully" so I don't understand why he's getting this message.

Thanks for any ideas.
0
Comment
Question by:synapse88
  • 3
7 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 22654760
One thought that comes to mind is there is a corrupt USER DSN (or artifacts of it) by the same name.   Check the USER tab of the ODBC control panel.  For grins, try using a different DSN name.
0
 

Author Comment

by:synapse88
ID: 22658784
I've already tried that and it was no help :(
0
 
LVL 1

Accepted Solution

by:
LukeB earned 500 total points
ID: 22902494
Sy,

I had / have similar problem ... I more or less have it tracked down.

I setup a DSN file that works fine for me and one other user but FAILS on all others :

   [ODBC]
   DRIVER=SQL Native Client
   UID=luke
   DATABASE= HGRDB
   WSID=DELLM90_C8WBL1S
   APP=Microsoft Data Access Components
   SERVER=SERVER-B\BEVTEMPSQL
   Description=HGRDB accounting

All other users get a "ODBC --call failed .  ODBC Driver Manager] Data source name was not found and no default drivers specified (#0)"

So I went to one of their machines and made another DSN, it looks like this:

   [ODBC]
   DRIVER=SQL Server
   UID=luke
   Trusted_Connection=Yes
   DATABASE=HGRDB
   WSID=BEVERLEY68
   APP=Microsoft Office 2003
   SERVER=SERVER-B\BEVTEMPSQL
   Description=Luke Test From User Machine

I am putting it down ot older machines that do not have the latest MS SQL Driver or something ...

Note that several lines are different. Give it a try , thise older format works for me (as did the first one I set up, more update version). It is working for me on a DSN files and now I am just changing my code to use the 'older' format. to make DSN-less connections.
0
 
LVL 1

Expert Comment

by:LukeB
ID: 22902506
PS: with the second one you can omit the Trusted_Connection line to begin with, SQLServer will  pop that up if the user is known or if you do not  have iintegrated SQLServer login enabled on the server.

Even if the user is not include in SQLServer ACL then the SQLServer login prompt will come up, I just tried the updated system on one of the users that had problems before and now they can connect to the SQLServer, I just need to give them a log in

good luck
0
 
LVL 1

Expert Comment

by:LukeB
ID: 22902510
PPS: and see  PAQ /Q_23396055.html, that tweaked me to try gong to the user's machines and ttrying that way.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now