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

• Microsoft Access 2000

SYMPTOMS

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

RESOLUTION

If your application or server requires that the IsRowGUID property is set to True, use the following workaround:

1. Create a view 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.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 2000.

MORE INFORMATION

Steps to Reproduce Behavior

1. Using any text editor, such as Notepad, type the following code into a text document, and 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 an MS-DOS 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 on the shortcut menu.



6. On a computer where Microsoft Access 2000 is installed, open a database, and then create a link to the MyAuthors table on the SQL Server computer.
   
   NOTE: This behavior has been reported on computers running Windows NT 4.0. and Windows 2000.



7. View the contents of the table in the Access 2000 database. Note that all records in the table display #DELETED.

Additional query words: pra # deleted

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug