ACC97: IPF When Using Execute Method with dbFailOnError

Last reviewed: February 3, 1998
Article ID: Q180348
The information in this article applies to:
  • Microsoft Access 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you run a Visual Basic for Applications procedure that uses the Execute method to update data in a Microsoft SQL Server ODBC data source, you may receive the following error message.

In Microsoft Windows NT 4.0

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

   MSACCESS.exe
   Exception: access violation (0xc0000005)

In Microsoft Windows 95

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

   When you click Details, you see the following message:

      MSACCESS caused an invalid page fault in module KERNEL32.DLL.

However, the action originated by the Execute method is completed successfully.

CAUSE

You receive this error message when all of the following conditions are true:

  • You are using the Execute method with the constant dbFailOnError.
  • You are updating a field with data that consists of a string longer than 60 characters. The length of the string that causes the error can vary, and you may receive the error when updating the field with a string longer than 45 characters.

WORKAROUND

Create a temporary SQL pass-through query in your Visual Basic for Applications procedure. Concatenate the text string with which you want to update the field into the SQL statement; set the SQL property of the QueryDef to the SQL statement. You can then use the Execute method of the QueryDef with the constant dbFailOnError. The following example demonstrates how to implement the procedure:

  1. Repeat steps 1-7 from the "Steps to Reproduce Behavior" section later in this article. If you link to the table in step 7, you can open the table later to verify that the procedure worked properly.

  2. Open a new module and type the following code:

          Function PassThruUpdate(strUpdateData As String)
    

             Dim db As Database
             Dim qd As QueryDef
             Dim strSQLString As String
             On Error GoTo Err_PassThruUpdate
             Set db = CurrentDb
             Set qd = db.CreateQueryDef("")
             ' Modify the connect string in the following line
             ' to reflect the ODBC data source you are using.
             qd.Connect = "ODBC;DSN=sqltest;UID=sa;PWD=;DATABASE=pubs"
             strSQLString = "UPDATE tbltoSQL SET StringTest = '"
             strSQLString = strSQLString & strUpdateData
             strSQLString = strSQLString & "' WHERE id = 'a'"
             qd.ReturnsRecords = False
             qd.SQL = strSQLString
             qd.Execute dbFailOnError
             db.Close
    
          Exit_PassThruUpdate:
             Exit Function
    
          Err_PassThruUpdate:
             MsgBox CStr(Err) & " " & Err.Description
             Resume Exit_PassThruUpdate
    
          End Function
    
    

  3. To test the sample function, type the following line in the Debug window, and then press ENTER:

          ?PassThruUpdate(string(75,"X"))
    

    Note that although you are using the Execute method with the constant dbFailOnError, and the string with which you are updating the field is longer than 60 characters, you do not receive the error described in the "Symptoms" section.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access 97 and create a new blank database.

  2. Create the following table:

       Table: tblToSQL
       ----------------------
       Field Name: ID
          Data Type: Text
          Field Size: 2
       Field Name: StringTest
          Data Type: Text
          Field Size: 100

       Table Properties: tblToSQL
       --------------------------
       PrimaryKey: ID

  3. Save the table as tblToSQL and open it in Datasheet view.

  4. Enter the following data into the tblToSQL table:

       ID  FullName
       --  --------
       a   aaa
       b   bbb

  5. Close the table.

  6. Export the table to a Microsoft SQL Server database.

  7. Link to the table that you just exported. When prompted to select a
     unique record identifier, click ID, and then click OK.

  8. Open a new module and type the following code:

       Function TestExecute(intUpdateLength As Integer)

          Dim db As Database
          Dim strSQLString As String
          Dim strUpdateString As String
          strUpdateString = String(intUpdateLength, "X")
          strSQLString = "UPDATE dbo_tblToSQL SET StringTest = '" & _
             strUpdateString
          strSQLString = strSQLString & "' WHERE id = 'a';"
          Set db = CurrentDb
          db.Execute strSQLString, dbFailOnError
          db.Close

       End Function

  9. On the Debug menu, click Compile And Save All Modules. When prompted
     to save the module, type "modTest" (without the quotation marks) and
     then click OK.

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

       ?TestExecute(60)

     Note that you receive the error message described in the "Symptoms"
     section.

REFERENCES

For more information about the Execute method, search the Help Index for "Execute method," and then display the "Execute Method (DAO)" topic.


Additional query words: Watson
Keywords : MdlGnrl MdlProb EvnGpf
Version : WINDOWS:97
Platform : WINDOWS
Hardware : x86
Issue type : kbbug
Solution Type : kbpending


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