Error message when you enter a large amount of text in a Memo field that contains an index in Access: "Run-time error '3709'"

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q302525
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.
SYMPTOMS
In a Microsoft Access database, when you create a Memo field that includes an index or you create a field name that includes the automatically indexed text, you cannot enter the text that includes more than approximately 3450 characters into the field. When you try to enter more text into the field or try to edit the existing data, you receive the following error message:
Run-time error '3709':

The search key not found in any record.
RESOLUTION
If you must enter a large amount of text in the Memo field, delete the index for the Memo field. To do so follow these steps:
  1. Open the table with the Memo field in Design view.
  2. On the View Menu, click Indexes.

    Note In Microsoft Office Access 2007, click the Design tab, and then click Indexes in the Show/Hide group.
  3. Click the index for the Memo field, and then delete it.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION
This behavior does not occur in versions of Microsoft Access before Microsoft Access 2000 because Memo fields could not be indexed in Jet 3.5 and earlier.

Steps to reproduce the behavior in Access 2003

  1. In a new database, create a new table with the following properties, and then save it as Table1:
       Table: Table1   ------------------------   Field Name: Id      Data Type: AutoNumber      Primary Key      Field Name: MyCode      Data Type: Memo					
  2. Add the following record to the Table1 table:
       Id        MyCode   ------------------------------------   1         This is the Memo test data					
  3. Copy the following SQL statement, paste it into a new query in SQL view, and then save the query as query1:
    UPDATE Table1 SET Table1.MyCode = [MyCode] & " " & [MyCode];
  4. Create the following module, and then save it Module1:
    Sub TestMemoUpdate()    Dim i As Integer    Docmd.setwarnings false    For i = 1 To 10    Docmd.openquery "Query1"       Next i    Docmd.setwarnings trueEnd Sub					
  5. Place your pointer so that it is in the procedure. Press F5 to run the code.
pra ACC2002 ACC2003 ACC2007
Properties

Article ID: 302525 - Last Review: 04/14/2007 01:57:10 - Revision: 5.1

Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbexpertiseinter kbtshoot kbvba kbbug kberrmsg kbnofix KB302525
Feedback