ACC97: #Deleted Entries Appear in Alternate Rows in Linked SQL Server Tables (269580)


The information in this article applies to:

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

SYMPTOMS

In a linked table from Microsoft SQL Server 6.5, Microsoft SQL Server 7.0, or Microsoft Data Engine (MSDE) you see #Deleted in alternate rows. One row has the typical data, the next contains #Deleted entries, the next typical data, and so on.

CAUSE

This behavior can occur under the following conditions:
  • The table has numeric or text field columns.
  • The SQL Server database has been created on a server with non-default code pages and sort order.
  • The version of the Microsoft ODBC SQL Server driver accessing the table is 3.70.0623.
NOTE: This problem does not occur with earlier or later versions of the Microsoft ODBC SQL Server driver.

RESOLUTION

To resolve this problem, do one of the following:
  • When you configure the data source name (DSN) in the ODBC Administrator, on the last page, clear the Perform translation for character data option.
  • Create a pass-through query in Access instead of using linked tables.

    NOTE: This only works if the data can be read-only.
  • Install a newer SQL Server ODBC driver by installing the latest version of Microsoft Data Access Components (MDAC). For more information about the latest version of MDAC and to download it, please visit the following Microsoft Web site:

    http://msdn.microsoft.com/dataaccess

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. On a clean Microsoft Windows NT Server or Microsoft Windows 2000 Server computer, start Setup of SQL Server 7.0.
  2. In the Setup Type dialog box, click Custom, and then click Next.
  3. In the Select Components dialog box, click Next.
  4. In the Character Set/Sort Order/Unicode Collation dialog box, set the following items:
    • Click Character Set, and then click 437 US English.
    • Click Sort Order, and then click Binary Order.
    • Make sure Locale Identifier is set to Binary Order.
    • Of the check boxes on the lower right, verify that Case-insensitive is the only item selected.

  5. Complete the Setup of SQL Server, and then restart the computer.
  6. On another computer that is running a clean installation of Microsoft Windows 95 or Microsoft Windows 98, install Access 97.
  7. With the ODBC Administrator, create a system data source name (DSN) for SQL Server 7.0, keeping the default settings, and specifying the Pubs sample database as the default database to connect to on the server.
  8. In Microsoft Access 97, link the Publishers and Pub_Info tables through the DSN that you created in the previous step.
  9. Open the linked tables. Note that #Deleted appears in alternate rows as described in the "Symptoms" section.
  10. Create a SQL specific pass-through query with the same DSN, and then run either of the following SQL statements:

    Select * from publishers

    -or

    Select * from pub_info

    Note that the data is returned correctly, unlike the data in the linked tables.
  11. Quit Access.
  12. Open the ODBC Administrator, and then reconfigure the system DSN so that the Perform translation for character data option check box is not selected (the check box is selected by default).
  13. Start Access again, and create links to two other Pubs tables. Note that the data is now displayed correctly.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

236825 PRB: #deleted Seen When SQL Server 7.0 Tables Containing Numeric and Text fields Are Opened

Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbbug kbnofix KB269580
©2003 Microsoft Corporation. All rights reserved.