ACC97: Search and Replace in Large Memo Field Causes IPF

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

SYMPTOMS

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

When you perform a search and replace in a Memo field containing more than 2052 characters, you receive one of the following error messages.

In Microsoft Windows 95

   This program has performed an illegal operation and will be shut
   down.

If you click the Details button, you see the following information:

   MSACCESS caused an invalid page fault in MSACCESS.EXE.

In Microsoft Windows NT

   An application error has occurred and an application error log is
   being generated. MSACCESS.exe

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

RESOLUTION

The following example uses an update query that calls a user-defined function to do the search and replace:

  1. Create a module and type the following line in the Declarations section if it is not already there:

          Option Explicit
    

  2. Type the following procedure:

        Function ReplaceString(strSearch As String, strSearchFor As String, _
           strReplaceWith As String)
        On Error GoTo err_ReplaceString
    
            ' Searches the strSearch variable for strSearchFor
            ' and replaces it with strReplaceWith.
            Dim lngFoundLoc As Long    ' Location of match.
            Dim lngLenRemove As Long   ' Length of string being replaced.
            ' Set length of original text to skip.
            lngLenRemove = Len(strSearchFor)
            ' Set location of match.
            lngFoundLoc = InStr(1, strSearch, strSearchFor)
            ' If strSearchFor isn't found in strSearch
            ' just return the original string.
            If lngFoundLoc = 0 Then
                ReplaceString = strSearch
            ' If match is found, return original string up to match
            ' location, concatenate new text, and search the rest of
            ' the string recursively for additional matches.
            Else
                ReplaceString = Left(strSearch, lngFoundLoc - 1) & _
                    strReplaceWith & _
                    ReplaceString(Mid(strSearch, lngFoundLoc + _
                    lngLenRemove), strSearchFor, strReplaceWith)
            End If
        exit_ReplaceString:
            Exit Function
        ' Print error to Debug window and don't interrupt query.
        err_ReplaceString:
            Debug.Print "Error Replacing String """ & _
                strSearchFor & """ with """ & _
                strReplaceWith & """ in text """ & _
                strSearch & """"
            ' If there is an error, return original string
            ' and exit the function.
            ReplaceString = strSearch
            Resume exit_ReplaceString
        End Function
    
    

  3. On the Debug menu, click "Compile and Save All Modules."

  4. Create a new query and add the table containing the Memo field:

          Query: qryUpdateMemos
          --------------------------------------------------------------------
          Type: Update Query
    

          Field: <Name of Memo Field>
    
             Table: <Name of Table>
             Update To:
              ReplaceString([<Name of Memo Field>],[Search for],[Replace with])
              Criteria:
          Field: Instr(1,[<Name of Memo Field>], [Search for])
             Update To:
             Criteria: >0
    
    

  5. On the Query menu, click Parameters.

  6. On the first line of the grid, type "[Search for]" (without the quotation marks) in the Parameter field, and select Text from the Data Type box.

  7. On the second line of the grid, type "[Replace With]" (without the quotation marks) in the Parameter field, and select Text from the Data Type box.

  8. Run the query. Enter the text to search for and the text to replace with when prompted.

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. Start Microsoft Access and open a new blank database.

  2. Create the following table:

          Table: tblMemoTable
          --------------------
          Field Name: MemoText
    
             Data Type: Memo
             Indexed: No
    
       Close the table and save it as tblMemoTable. When prompted if you want
       to create a primary key, click No.
    
    

  3. Create a new module and type the following procedure:

          Function FillMemo(strTableName As String, _
    
             strFieldName As String)
    
             Dim db As Database
             Dim rs As Recordset
             On Error GoTo Err_FillMemo
             Set db = CurrentDb
             Set rs = db.OpenRecordset(strTableName)
             With rs
                .AddNew
                .Fields(strFieldName) = "abc" & String(2050, "x")
                .Update
             End With
             db.Close
          Exit_FillMemo:
             Exit Function
    
          Err_FillMemo:
             MsgBox CStr(Err) & " " & Err.Description
             Resume Exit_FillMemo
          End Function
    
    

  4. Type the following line in the Debug window, and then press ENTER:

          ?FillMemo("tblMemoTable","MemoText")
    

  5. Open tblTableMemo.

  6. On the Edit menu, click Replace.

  7. In the Replace In Field dialog box, type "abc" (without the quotation marks) in the Find What text box.

  8. Type "hij" (without the quotation marks) in the Replace With text box, and then click to clear the Match Whole Field check box.

  9. Click Replace. Note that you receive one of the errors described in the "Symptoms" section.

REFERENCES

For more information about creating update queries, search the Help Index for "update queries, creating" and select the Help Topic "Change records as a group using an update query," or ask the Microsoft Access 97 Office Assistant "How to create an update query."

For more information about writing recursive procedures, search the Help Index for "recursive procedures" and select the Help topic "Creating Recursive Procedures."


Additional query words: pra searching replacing
Keywords : GnlFnd kberrmsg
Version : WINDOWS:97
Platform : WINDOWS
Hardware : x86
Issue type : kbbug
Solution Type : kbcode kbworkaround


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: October 17, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.