Microsoft KB Archive/327268

= ACC2000: Errors Occur When Using File DSNs in Access 2000 =

Article ID: 327268

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q327268



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
After you successfully create, and then view a linked Open Database Connectivity (ODBC) table in Microsoft Access by using a file data source name (DSN), one of the following error messages is generated after you restart Access, and then try to open one of the previously created linked tables:

Could not execute query. Could not find linked table. [Microsoft][ODBC][SQLServer] Invalid object name ' ': (#208)

-OR-

ODBC--Connection to ' ' failed.

Then, the following error message occurs:

Microsoft Access can't open the table in Datasheet view.



CAUSE
The Connect property of the table does not have enough information about the File DSN or the connection properties.



RESOLUTION
Use a Machine DSN (that is, a User DSN or a System DSN) instead of a File DSN.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
Depending on the ODBC data source, you may possibly resolve the problem by modifying the Connect property of the linked table programmatically. Although the exact connection properties vary depending on the ODBC data source, the following Microsoft Knowledge Base article discusses the required properties for the Microsoft SQL Server and the general process that applies for all linked ODBC tables:

208295 ACC2000: Procedure to Create Data Sources and Relink ODBC Tables

Steps to Reproduce the Behavior

 * 1) Create a new nonintegrated SQL Server 2000 logon.
 * 2) Set the default database of the new user to Northwind (or another user database), and then grant the user appropriate permissions.
 * 3) Create a new File DSN by using the security credentials of the user from step 1.
 * 4) Open a .mdb file in Access, and then create a linked table by using the DSN from step 3.

Note that the link works correctly after the user first connects.
 * 1) Close Access, and then reopen the .mdb file from step 4.
 * 2) Open the linked table that you created in step 4.

You receive the error message that is mentioned in the &quot;Symptoms&quot; section of this article.

