Microsoft KB Archive/121834

= ACC: Linked SQL Server Binary Data Displayed Incorrectly =

Article ID: 121834

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q121834





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

When you link (attach) a 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 seems 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 Microsoft 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 to one of these binary fields, Microsoft Access inserts ASCII characters, instead of binary data.



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 1.0, 1.1, 2.0, 7.0 and 97.



Steps to Reproduce Problem
This article assumes that you are familiar with the SQL language and with creating tables in an SQL database using the programming tools provided with Microsoft SQL Server. For more information about the SQL language, please refer to the documentation provided with your Microsoft SQL Server.

  Use one of the following two methods to create an 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 2.0, 7.0 and 97 only):

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)  Link the resultant table in Microsoft Access and view it in Data- sheet view. Note that the data in the first row is displayed as "99," and that nothing is displayed in the second row. (The second row actually contains unprintable characters that you cannot see.)

