Microsoft KB Archive/170218

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 15:35, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Article ID: 170218

Article Last Modified on 1/20/2007


  • Microsoft Access 97 Standard Edition

This article was previously published under Q170218

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.


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 (Q139487)
                 2.65.0213  Microsoft Access 97 (Q159674)
                 2.65.0240  SQL Server 6.5

To obtain the latest drivers, install the Microsoft Data Access Components (MDAC) 2.0 from the following Microsoft Web site:


Remove the criteria you have set on the indexed field.


Microsoft has confirmed this to be a problem in Microsoft Access 97.


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.

Additional query words: prb pra

Keywords: kbbug kbinterop kbnofix KB170218