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

MORE INFORMATION

Steps to Reproduce Problem

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




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.

Additional query words: attaching

Keywords          : OdbcSqlms 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: April 8, 1999