ACC: Linked SQL Server Binary Data Displayed Incorrectly
ID: Q121834
|
The information in this article applies to:
-
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
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.
Additional query words:
attaching
Keywords : OdbcSqlms
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbbug