ACC2: Errors Using Large OLE Objects with MS SQL Server Tables

ID: Q132028

The information in this article applies to:

Microsoft Access 2.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use large OLE objects (greater than about 400K) with Microsoft SQL Server tables, you may encounter the following problems. If you insert the OLE object into an attached Microsoft SQL Server table, you receive the following two error messages:

ODBC call failed

The insert/update of a text or image column(s) did not succeed.

When you export a table containing a large OLE object to a Microsoft SQL Server table, you receive the following error message

Errors were encountered copying data: The contents of fields in x records were deleted and 0 record(s) were lost due to key violations. Proceed anyway?

and the OLE object is truncated.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Steps to Reproduce Problem

Create a table in Microsoft Access with the following structure:


      Table: TestExport
      -----------------
      Field Name: Field1
         Data Type: Text
         Indexed: Yes (No Duplicates)
      Field Name: Field2
         Data Type: OLE Object

Export the TestExport table to Microsoft SQL Server.

Create a unique index on the TestExport's Field1 field using an SQL pass-through query with the following syntax:
```
      create unique index TestExport_Ind on TestExport(Field1) 
```

Attach to the TestExport table on Microsoft SQL Server.

Open the attached TestExport table in Datasheet view.

In the first record, type any text value in the Field1 field, and then move the mouse pointer to the Field2 field.

On the Edit menu, click Insert Object.

In the Insert Object box, click the Insert From File option button, and then enter a path to a file containing an OLE object larger than 400K.

Move the mouse pointer to the second record. Note that when Microsoft Access tries to save the first record, you receive the following error messages:

ODBC call failed.

the insert/update of a text or image column(s) does not succeed.

REFERENCES

For more information about using SQL Server tables, search for "SQL Server," and then "Importing and Attaching SQL Database Tables" using the Microsoft Access Help menu.

Additional query words: string data right truncation

Keywords : kberrmsg kbusage OdbcSqlms
Version : 2.0
Platform : WINDOWS
Issue type : kbbug