ACC2000: Records Display #DELETED When You Use Access 2000 to Link to SQL Server Table with ROWGUID

When you use Access 2000 to link a Microsoft SQL Server table that contains a UniqueIdentifier column as the primary key, #DELETED appears for all records in the result set.

To resolve this problem, install the latest Jet 4.0 service pack. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: If your application or your server requires that the IsRowGUID property is set to True, and you cannot install the latest Jet 4.0 service pack, use the following workaround:

1. Create a view by using the server table.
2. Link to this view from Access 2000.
3. Note that Access prompts you for a field to use as the primary key. Select a column other than the column that is the UniqueIdentifier data type.

NOTE: The view now displays the records without #DELETED in the rows.

Microsoft has confirmed that this is a problem in Microsoft Access 2000. This problem was corrected in the latest Jet 4.0 service pack.

Steps to Reproduce the Behavior

1. Use any text editor such as Notepad, and then type the following code in a text document. Save the text document as Q244872.sql in the MSSQL7 directory:

   USE PUBS
   go
      
   sp_dboption PUBS, 'SELECT INTO', true
   go   
     
   SELECT * INTO MYAuthors
   FROM   Authors
   go
   					

2. At the command prompt, change to the MSSQL7 directory.
3. Use the following line to run the Q244872.sql script from the osql utility:

   osql /U sa /P  /i Q244872.sql
   					

4. In Enterprise Manager, add a new column to the MyAuthors table as follows:
   Name: UniqueIdentifierCol
   DataType: Unique Identifier
   IsRowGUID: True
5. Right-click the UniqueIdentifierCol column, and then click Set Primary Key.
6. On a computer where Access 2000 is installed, open a database, and then create a link to the MyAuthors table on a computer that runs SQL Server.
   
   NOTE: This behavior has been reported on computers that run Microsoft Windows NT 4.0. and Microsoft Windows 2000.
7. View the contents of the table in the Access 2000 database. Note that all records in the table display #DELETED.