ACC2000: Errors Occur When Using File DSNs in Access 2000 (327268)


The information in this article applies to:

  • Microsoft Access 2000
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 'Table_Name': (#208)

-OR-

ODBC--Connection to 'DataSourceName' 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.
  5. Close Access, and then reopen the .mdb file from step 4.
  6. Open the linked table that you created in step 4.

    You receive the error message that is mentioned in the "Symptoms" section of this article.

REFERENCES

For additional information about other problems you may experience with File DSNs, click the article numbers below to view the articles in the Microsoft Knowledge Base:

212886 ACC2000: Using File DSN to Import or Link a FoxPro ODBC Data Source Causes Reserved Error -7778

201268 ACC2000: Select Data Source Dialog Reads Floppy and CD-ROM Drives Repeatedly

Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbbug kbpending KB327268
©2004 Microsoft Corporation. All rights reserved.