ACC: 'TMP%#MAU' Error Generated When Opening Linked Table (167849)

CAUSE

When you link to an ODBC Data Source, the Microsoft Jet database engine calls SQLSpecialColumns for the table you are linking. If SQLSpecialColumns returns a column name, but does not include that column name in the meta- data returned by SQLColumns, an internal state variable for the Column ID of that column is not initialized to a valid value for a Column ID. When you try to open the linked table, the Jet database engine tries to get information about the invalid Column ID. If it does not resolve to a column in the meta-data that is returned by the ODBC Driver, the Jet database engine tries to refresh the meta-data, creating a temporary table called TMP%#MAU. That table already exists, so the error message occurs, and you cannot open the table.

RESOLUTION

There are two ways you can work around this problem:

Use an SQL pass-through query to manipulate the data in your data source, which bypasses the Jet database engine.
You can use an ODBC driver that does not return special columns, such as row identifiers, that are not included in the table meta-data.

Another solution that has been reported by some customers is to activate ODBC Tracing in the ODBC Data Source Administrator before linking the table.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0 and 97. This problem no longer occurs in the updated version of the file, Msjet35.dll. The updated version of the file can be obtained from the following sources:

Updated version of Microsoft Jet 3.5 (Jet35SP3.exe).

For more information about how to obtain the Microsoft Jet 3.5 update, please see the following article in the Microsoft Knowledge Base:

172733: ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
Microsoft Office 97 for Windows, Service Release 1 (SR-1)

For more information about how to obtain SR-1, please see the following article in the Microsoft Knowledge Base:

172475 OFF97: How to Obtain and Install MS Office 97 SR-1
Stand-alone Microsoft Access 97 Service Release 1

NOTE: Stand-alone Microsoft Access 97 Service Release 1 is not currently available, but you can update your copy of Microsoft Access 97 to Service Release 1 by downloading Microsoft Office Service Release 1 from Microsoft's Web site at:
http://office.microsoft.com/en-us/assistance/HA010449921033.aspx

MORE INFORMATION

With the Microsoft Jet database engine version 3.5, the value in the uninitialized Column ID variable is most often outside the range of column ordinal values, so the special column is not found and the Jet database engine tries to refresh the meta-data. However, this occurs within the meta-data refresh code itself, and the code is not designed to be called recursively. So when the Jet database engine tries to create a temporary table to retrieve the meta-data, it fails because the temporary table already exists.

This behavior differs from the Microsoft Jet database engine version 3.0 that ships with Microsoft Access for Windows 95 version 7.0. With that version of the Jet database engine, the value in the uninitialized Column ID variable is typically within the range of column ordinal values in the table being accessed. Therefore the special column is found, no attempt is made to refresh the meta-data, and the table opens successfully.

REFERENCES

For more information about SQL pass-through queries, search the Help Index for "pass-through queries," and see the following articles in the Microsoft Knowledge Base:

112108 ACC: How to Create an SQL Pass-Through Query in Code

124391 ACC: Sample Code for Running Temporary SQL Pass-Through Query

131534 ACC How to Simulate Parameters in an SQL Pass-Through Query