ACC2000: Can't Delete, Rename, or Edit Table in SQL 6.5 Enterprise Manager (202786)


The information in this article applies to:

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

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

SYMPTOMS

If you use Access 2000 Client-Server to create a table that violates SQL Server naming conventions, you will be unable to delete, rename or edit the table in SQL Server 6.5 Enterprise Manager. If you try to do so, you receive the following error message:
Error 21770: The name <table name> was not found in the specified collection.

CAUSE

Access Client-Server automatically executes the SET QUOTED_IDENTIFIER Transact-SQL statement, which allows you to create and use objects in an Access project with names that do not follow the SQL Server 6.5 object identifier rules.

However, SQL Server 6.5 Enterprise Manager does not recognize objects that do not meet the criteria for object identifiers in SQL Server 6.5.

RESOLUTION

Using Microsoft Access Client-Server or ISQL_w, rename the table in question using a name that follows SQL Server 6.5 object identifier rules.

To Rename a Table in Access 2000 Client-Server

  1. In Access 2000, open an Access project connected to the database containing the table that you want to rename.
  2. In the table list, right-click the table that you want to rename, and then click Rename on the shortcut menu.
  3. Give the table a new name, and then press ENTER.

To Rename a Table with ISQL_w

NOTE: ISQL_w is one of the client tools included with Microsoft SQL Server 6.5. It is not included with Microsoft Office 2000 or Access 2000.
  1. Click Start, point to Programs, point to Microsoft SQL Server 6.5, and then click ISQL_w.
  2. Connect to the SQL Server hosting your database by providing a Server name, Login ID, and Password. Click Connect.
  3. Enter the following script in the Query window of ISQL_w, substituting MyDatabase with the name of your database, My Old Table Name with the current name of your table, and MyNewTableName with the new name to give the table:
       Use MyDatabase
   GO
   SET QUOTED_IDENTIFIER ON
   EXEC sp_rename 'My Old Table Name','MyNewTableName'
  4. On the Query menu of ISQL_w, click Execute

REFERENCES

For more information on the SET QUOTED_IDENTIFIER statement, refer to the "Identifiers" Help topic in SQL Server 6.5 Books Online.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:KbClientServer kberrmsg kbprb KB202786
©2004 Microsoft Corporation. All rights reserved.