Microsoft KB Archive/244872

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

Article ID: 244872

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q244872



Advanced: Requires expert coding, interoperability, and multiuser skills.



SYMPTOMS
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.



RESOLUTION
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:

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

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.



STATUS
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
  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  At the command prompt, change to the MSSQL7 directory.  Use the following line to run the Q244872.sql script from the osql utility: osql /U sa /P /i Q244872.sql  In Enterprise Manager, add a new column to the MyAuthors table as follows:

Name: UniqueIdentifierCol

DataType: Unique Identifier

IsRowGUID: True

 Right-click the UniqueIdentifierCol column, and then click Set Primary Key.</li> 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.</li> View the contents of the table in the Access 2000 database. Note that all records in the table display #DELETED.</li></ol>

Additional query words: pra # deleted sql server

Keywords: kbbug KB244872

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.