ACC: Linked SQL Server Binary Data Displayed Incorrectly

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

When you link (attach) a SQL Server table containing 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

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.

1. Create an SQL table called TMS by one of the two following methods:

   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)
   

2. 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.)

REFERENCES

For more information about pass-through queries, search for "pass-through queries, creating" and then "Send commands to an SQL database using a pass-through query" using the Microsoft Access 97 Help Index.

For more information about SQL Server and Access, search for "SQL databases," and then "Using the MSysConf table with linked SQL databases" using the Microsoft Access 97 Help Index.