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

• Microsoft Access version 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.

      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?

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

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

2. Export the TestExport table to Microsoft SQL Server.

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

4. Attach to the TestExport table on Microsoft SQL Server.

5. Open the attached TestExport table in Datasheet view.

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

7. On the Edit menu, click Insert Object.

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

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