Microsoft KB Archive/208900

= ACC2000: Linked SQL Server Binary Data Displayed Incorrectly =

Article ID: 208900

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.



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:   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.  Open any database and, on the File menu, point to Get External Data, and then click Link Tables. Select the ODBC data source in which you created the tms table. 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.

<div class="references_section">