ACC: Linked SQL Server Binary Data Displayed Incorrectly (121834)

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.

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.

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

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.