"Run-Time Error '3709'" error message when you enter a large amount of text in a Memo field that contains an index (302525)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q302525
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you create a Memo field in a Microsoft Access database and create an index on that field (or you create a field name that includes text that is automatically indexed), you can enter only a specific amount of text. Once you reach this limit (approximately 3450 characters), you can enter no more text and you can also not edit the existing data. If you try to do so, 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.
  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

  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 true
End Sub
  5. Place your pointer so that it is in the procedure. Press F5 to run the code.

    Note that you receive the following error message:
    Run-time error '3709':

    The search key not found in any record.
Modification Type:MinorLast Reviewed:6/8/2004
Keywords:KbVBA kbbug kberrmsg kbnofix KB302525 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.