ACC2: "Couldn't Find Object '<Name>'" with SQL Server Table

• Microsoft Access versions 2.0

SYMPTOMS

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

When you try to link (attach) a SQL Server table, you may receive the following error message:

   Couldn't find object '<tablename>'

CAUSE

This problem occurs only if you have installed the Microsoft Access version 2.0 Service Pack. This problem is caused by the Microsoft SQL Server ODBC driver, but occurs only if you are using the Microsoft Jet database engine version 2.5, which is installed with the Service Pack. The problem with the SQL Server driver causes the Microsoft Jet database engine version 2.5 to be unable to link (attach) any table with a name with the following pattern:

   (number of non-underscore characters in table name) + ((number of
   underscores in table name) x 3) >= 33

   a_bceghi_jklopq_stuv_x24

   a_bcdeghi_jklopq_stuv_x25

RESOLUTION

To work around this problem, rename the table so that it does not have a name matching the pattern described in the "Cause" section. Or, create a SQL Server view based on the table and then link the view. For example, you could use the following sample SQL statement to create a view:

   create view AbcDefGhiJklMnoPqr
   as
   select * from abc_def_ghi_jkl_mno_pqr;

   create unique index Index1 on <owner>_AbcDefGhiJklMnoPqr (field1)

STATUS

This problem no longer occurs with the ODBC SQL Server driver that ships with Microsoft Access 7.0 or 97 and SQL Server 6.x.

REFERENCES

For more information about the Microsoft Access version 2.0 Service Pack, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122927
   TITLE     : WX1124: Microsoft Access Version 2.0 Service Pack

   ARTICLE-ID: Q147192
   TITLE     : Which ODBC Driver Do I Use and Where Do I Get It?