ACC97: SQL Memo Fields Truncated to 255 in Update Query

 Last reviewed: June 17, 1997
Article ID: Q170218
The information in this article applies to:
  • Microsoft Access 97

SYMPTOMS

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

When you run an update query from Microsoft Access to copy the contents of one SQL Server Memo field into another SQL Server Memo field, the data in the new field will be truncated at 255 characters.

CAUSE

This behavior will occur when you add criteria to one of the SQL Server fields, that field is part of an index, and you are using any of the following 32-bit ODBC drivers: 

   Driver        Version    Source
   ------------  ---------  ---------------------------------------------
   SQLSRV32.DLL  2.50.0126  Microsoft Access 7.0
                 2.65.0201  ODBC Desktop Driver Pack 3.0 (Appnote WX1220)
                 2.65.0213  Microsoft Access 97 (Appnote WX1350)
                 2.65.0240  SQL Server 6.5

   For more information about obtaining Appnote WX1220, please see the
   following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q139487
   TITLE     : WX1220: ODBC 32-Bit Drivers Appnote w/Desktop Drivers Ver.
               3.4

   For more information about obtaining Appnote WX1350, please see the
   following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q159674
   TITLE     : WX1350: ODBC Desktop Database Drivers 3.5 for 32-Bit
               Programs

RESOLUTION

Remove the criteria you have set on the indexed field.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

  1. Create a table called TEST in MS SQL Server 6.5 with the following structure: 

          Key   ColumnName   Datatype  Size  Nulls  Default
      ----  -----------  --------  ----  -----  -------
       Y    EmployeeID   int       4
            LastName     char      25
            Notes        text      16      Y
            Notes2       text      16      Y

  2. Open the sample database Northwind.mdb.

  3. Create a link to the Test table you created in step 1.

  4. Create an append query that appends the EmployeeID and Notes field from the Employees table to the EmployeeID and Notes field in the Test table. This will provide sample data for the SQL table.

  5. Create an update query based on the dbo_test table in Microsoft Access by typing the following syntax in the SQL View window: 

          UPDATE dbo_Test SET dbo_Test.Notes2 = [dbo_test]![notes]
      WHERE (((dbo_Test.EmployeeID)=2));

  6. Run the query.

  7. Open the dbo_Test table to view the results.

    Note that the query updates the Notes2 Memo field with the data from the Notes Memo field. However, the data in the Notes2 field is truncated at 255 characters.

Keywords : kbinterop OdbcSqlms
Version : 97
Platform : WINDOWS
Hardware : x86
Issue type : kbbug
Resolution Type : Info_Provided

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 17, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.