Microsoft KB Archive/161015

= ACC97: ODBC Error Using IDC Files to Query SQL Server Tables =

Article ID: 161015

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q161015



Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
When you use IDC files to query a table in a Microsoft Access 2.0, 7.0, or 97 database that is linked to Microsoft SQL Server, you may receive the following error message if the SQL Server password is not saved with the linked table when you create it:

  ODBC -- connection to '' failed.

This happens even if you specify a username and password in the IDC file.



CAUSE
The IDC file uses a System DSN to communicate with the Microsoft Access database that contains the linked tables. The username and password in the IDC file are used to log on to the Microsoft Access database. The Microsoft Access database cannot use the same username and password to log on to Microsoft SQL Server.



RESOLUTION
There are two methods to work around this behavior.

Method 1
Create a System DSN to query the Microsoft SQL Server database directly instead of using the Microsoft Access database with linked tables. Rewrite your IDC file so it uses the new System DSN. Then the username and password in your IDC file are used to log on to SQL Server.

Method 2
Save the password with the linked table when you create it in the Microsoft Access database.

The following example shows you how to save a password with a linked SQL Server table. This example assumes that you have a valid System DSN on your computer that points to the sample Pubs database on a Microsoft SQL Server.


 * 1) Start Microsoft Access and open the sample database Northwind.
 * 2) On the File menu, point to Get External Data, and then click Link Tables.
 * 3) In the Link dialog box, select ODBC Databases in the Files Of type box.
 * 4) In the Data Source dialog box that appears, select the System DSN that points to the Pubs database on your SQL Server, and then click OK.
 * 5) In the SQL Server Login dialog box, type a valid Login ID and Password, and then click OK.
 * 6) In the Link Tables dialog box, click dbo.authors, click to select the Save password check box, and then click OK.
 * 7) The linked table appears in the Northwind table list, and the SQL Server username and password is saved with it. Now you can query the linked table with an IDC file.



MORE INFORMATION
Use the work around described in Method 1 of the "Resolution" section when the error message occurs under the following circumstances:


 * If your IDC file queries a linked SQL Server table in a Microsoft Access 2.0 database, even if you save the password with the linked table.
 * If your IDC file queries a linked SQL Server table in a remote Microsoft Access database on a Microsoft Windows 95 computer (a different computer than the web server), even if you save the password with the linked table.

