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

• Microsoft Access 97

SYMPTOMS

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

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 '<Server Name>' failed.

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.

REFERENCES

For more information about linking tables to ODBC data sources, search the Help Index for "ODBC, importing and linking data," or ask the Microsoft Access 97 Office Assistant.