ACC2000: Linked SQL Server Binary Data Displayed Incorrectly (208900)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208900
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

When you link a Microsoft SQL Server table that contains fields with Binary, Varbinary, or Timestamp data types, the data in these fields is displayed as seemingly random characters. Also, when you insert data from Microsoft Access into these fields in the SQL Server table, the data appears to consist of random characters.

CAUSE

Microsoft Access assumes that the data in the linked SQL Server table is stored in ASCII format. However, the data in Binary, Varbinary, and Timestamp fields is stored in binary format, not ASCII format. When Access displays the data in these fields, it displays ASCII equivalents for the data in the fields, resulting in seemingly random characters being displayed.

When you try to insert data into one of these binary fields, Access inserts ASCII characters instead of binary data.

STATUS

Microsoft has confirmed that this is a problem in Access.

MORE INFORMATION

Steps to Reproduce the Problem

This article assumes that you are familiar with Structured Query Language (SQL) and with creating tables in a SQL database using the programming tools provided with Microsoft SQL Server. For more information about SQL, please refer to the documentation provided with your SQL Server product.

To create a SQL table containing binary data, link it to your current database, and view the results, follow these steps:
  1. Use one of the following two methods to create a SQL table called tms:

    Method 1:

    Create the following script table on a SQL Server:

    create table tms(i int, b binary(8))
    go
    create unique index idx on tms(i)
    go
    insert tms values(1,0x3939)
    insert tms values(2,0x00019d01)

    Method 2 (In Microsoft Access 2000):

    Create a pass-through query and run the following commands, one at a time:

    create table tms(i int, b binary(8))
    create unique index idx on tms(i)
    insert tms values(1,0x3939)
    insert tms values(2,0x00019d01)

    Choose a local ODBC data source in which to create the table, index, and the two records.
  2. Open any database and, on the File menu, point to Get External Data, and then click Link Tables.
  3. Select the ODBC data source in which you created the tms table.
  4. Open the linked tms table in Datasheet view.

    Note that the data in the i field (Long Integer data type) is displayed correctly as "1" and "2", but that the data in the b field (Binary data type) is not the expected binary data. If you create a new record, the i field accepts only integers, but the b, or binary, field accepts any sort of data, even text.

REFERENCES

For more information about pass-through queries, click Microsoft Access Help on the Help menu, type send commands to an sql database using a pass-through query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about SQL Server and Access, click Microsoft Access Help on the Help menu, type use the msysconf table with linked sql databases in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:7/16/2004
Keywords:kbbug kbpending KB208900
©2004 Microsoft Corporation. All rights reserved.