"Can't Access Table" Error If Table Name Contains Underscores

ID: Q124500

The information in this article applies to:

SYMPTOMS

If you are using the SQL Server ODBC driver with Microsoft Query to view a table from an SQL Server database, you may receive the following error message 

   Can't access table <table_name>
where <table_name> is the table you are trying to view.

CAUSE

Your ability to access a table from Microsoft Query is affected by the number of underscores and the length of the SQL Server table name.

STATUS

Microsoft has confirmed this to be a problem in versions 1.0 and 2.0 of the Microsoft SQL Server ODBC drivers for Windows and Macintosh.

The problem is corrected in the 32-Bit SQL Server ODBC driver (version 2.50.0121) that is included with the ODBC 3.0 Desktop Driver pack.

For additional information, please see the following article(s) in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q140255
   TITLE     : GE1263: Microsoft ODBC Drivers for 32-Bit Programs

MORE INFORMATION

Microsoft Query allows you to create table names up to 30 characters long; however, although Query will allow you to create a table that has underscores in the table name, you may not be able to view it later. You may not be able to view the table, because the SQL Server ODBC driver has a problem accessing tables in which the table name length exceeds a certain number of characters and the name also contains a certain number of underscores. The following table illustrates the limitations imposed when you access an SQL Server table in Microsoft Query. 

If the table name has    The maximum table name
this many underscores    length that can be viewed is


      1                              30
      2                              28
      3                              26
      4                              24
      5                              22
      6                              20
      7                              18
      8                              16
      9                              14
     10                              12
     11                              10
For example, if you try to view a table for which the name has a total of 25 characters, including 4 underscores, then you will receive the above error message.

NOTE: The last entry in the table shows that if there are 11 underscores in the table name and the table name is greater than 10 characters long, you will not be able to view the table after creating it. If the table has 11 underscores, the name cannot be less than 11 characters. Therefore, you cannot have a table with more than 10 underscores in the name.

Additional query words: 1.00 2.00 5.00 5.00a 5.00c 7.00 

Keywords          : xlquery 
Version           : 1.00 2.00
Platform          : WINDOWS

Last Reviewed: June 1, 1999