ACC2002: Table Names Displayed Incorrectly in a Microsoft Access Project (282367)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282367
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

Table names in Microsoft Access project may be displayed incorrectly. They may be inside quotation marks or may appear broken, with their parts transposed and inside parenthesis. For example, the table name Categories may be displayed in ways similar to the following:

"Cate)gories"

-or-

"gories""("Cate")

CAUSE

This behavior occurs when a table name contains characters that are not allowed in Microsoft Access project table names, such as a period (.), an exclamation point (!), an accent grave (`), brackets ([ ]) or a double quotation mark (").

RESOLUTION

If a table name in your Microsoft Access project contains one or more of the disallowed characters, you can still change it back to its original name or to any other valid name. To do so, use one of the following methods.

NOTE: If any of the existing queries, views, user-defined functions, stored procedures, or programs refer to the misnamed table, the name modification will make these objects invalid.

Method 1

This method requires that you have access to the Microsoft SQL Server where your database is located and the necessary permissions to delete and create tables in your database.
  1. Start the SQL Server Enterprise Manager.
  2. Expand the ServerName, Databases, DatabaseName, and then Tables.
  3. Locate the table in the table list in the right pane.
  4. Right-click the table, and then click Rename on the shortcut menu.
  5. Rename your table to a new name, and then press ENTER.
  6. Quit the SQL Server Enterprise Manager.

Method 2

This method allows you to change the table name directly from Microsoft Access. Initially, a system table is queried to establish the correct spelling of the table name as it exists on the server. A stored procedure is then used to rename the table. The example shown here is based on the "Steps to Reproduce the Behavior" section of this article.
  1. Open your Microsoft Access project.
  2. In the Database window, click Queries under Objects.
  3. Double-click Create view in designer. Click Close, and then click SQL on the toolbar.
  4. Type the following text in the SQL pane:
       SELECT name
   FROM sysobjects
   WHERE xtype = 'U'
  5. On the Query menu, click Run. Before the query runs, you are prompted to save the view. Save the view as qryUserTables.
  6. Examine the list of tables and locate the table with a period, a bracket, a quotation mark, an exclamation mark, or a grave accent in its name, for example, Cate".gories. This table would be incorrectly displayed in the Access Database window as "gories""("Cate")". Note the exact spelling.
  7. Press ALT+F11 to open the Visual Basic Editor.
  8. Press CTRL+G to open the Immediate window.
  9. Enter the following command: 
    CurrentProject.Connection.Execute "sp_rename '[Cate"".gories]', 'Categories'"
    You must enter the double quotation mark (") in the table name twice (that is, ""), and you must include the whole table name in square brackets ([]).
  10. Press ENTER to carry out the command.
  11. Press ALT+F11 to switch to Access.
  12. In the Database window, press F5 to refresh the view.

    The table name has been changed.

MORE INFORMATION

Microsoft Access does not allow object names containing periods and double quotations, and prevents such names from being created by means of the user interface. However, the SQL Server allows all of these characters in its table names and displays the names correctly. Object names may be changed by code or by a user in the SQL Server user interface. When such objects exist in SQL Server based databases, their names will not be displayed correctly in the Database window of your Access project.

Steps to Reproduce the Behavior

  1. Start Microsoft Access and open the sample project NorthwindCS.adp connected to SQL Server 2000.
  2. Press ALT+F11 to open the Visual Basic Editor.
  3. Press CTRL+G to open the Immediate window.
  4. To change the name "Categories" to "Cate".gories", type the following code in the Immediate window, and then press Enter: 
    CurrentProject.Connection.Execute "sp_rename 'Categories', 'Cate"".gories'"
    NOTE: You must use "" to designate a double quotation mark (").
  5. Close the Visual Basic Editor to return to the Database window.
  6. Press F5 to refresh the table list.
Notice that the table name listed in the Database window is:

"gories""("Cate")

REFERENCES

For more information about naming rules, click Microsoft Access Help on the Help menu, type Guidelines for naming fields, controls, and objects in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbprb KB282367
©2004 Microsoft Corporation. All rights reserved.