BUG: Records display #Deleted when you use Access 2002 to link to a SQL Server table that contains ROWGUID (290332)


The information in this article applies to:

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

This article applies only to a Microsoft Access database (.mdb).


For a Microsoft Access 2000 version of this article, see 244872.

SYMPTOMS

When you use Microsoft Access 2002 to link a Microsoft SQL Server 7.0 or a Microsoft SQL Server 2000 table that contains a UniqueIdentifier column as the primary key, #Deleted is displayed in every record of the linked table.

RESOLUTION

This problem was corrected in Microsoft Jet 4.0 Service Pack 5 and later. For additional information about how to obtain the latest Jet 4.0 service pack, click the following article number to view the article in the Microsoft Knowledge Base:

239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

STATUS

Microsoft has confirmed that this is a problem in Microsoft Access 2002.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Using any text editor, such as Notepad, type the following code into a text document:
    USE Pubs
GO
   
sp_dbOption Pubs, 'SELECT INTO', True
GO   
  
SELECT * INTO MyAuthors FROM Authors
GO
  2. If you are using SQL Server 2000, save the text file as Q290332.sql in the folder C:\Program Files\Microsoft SQL Server\MSSQL. If you are using SQL Server 7.0, save the text file as Q290332.sql in the folder C:\MSSQL7.
  3. At an MS-DOS command prompt, change to the appropriate folder (as indicated in the previous step).
  4. Use the following command line to run the Q290332.sql script from the osql utility:
    osql /U sa /P  /i Q290332.sql
  5. In Enterprise Manager, add a new column to the MyAuthors table as follows:

    Column Name: UniqueIdentifierCol
    Data Type: uniqueidentifier
    Is RowGUID: Yes

  6. Right-click the UniqueIdentifierCol column, click Set Primary Key on the shortcut menu, and then save your changes.
  7. On a computer on which Microsoft Access 2002 is installed, open a database, and then create a link to the MyAuthors table in the SQL Server database.
  8. View the contents of the table in the Access 2002 database. Note that all records and every field value displays #Deleted.
Modification Type:MinorLast Reviewed:8/10/2004
Keywords:kbtable kbbug kbnofix KB290332 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.