ACC97: IPF When Using Execute Method with dbFailOnError (180348)
The information in this article applies to:
This article was previously published under Q180348 Advanced: Requires expert coding, interoperability, and multiuser
skills.
SYMPTOMS 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: - 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.
- Open a new module and type the following code.
Note In the following sample code, you must change UID=<username> and
PWD=<strong password> to the correct values. Make sure that the user ID
has the appropriate permissions to perform this operation on the database. 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=<username>;PWD=<strong password>;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
- 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.
REFERENCES For more information about the Execute method, search the
Help Index for "Execute method," and then display the "Execute Method (DAO)"
topic.
Modification Type: | Minor | Last Reviewed: | 1/26/2005 |
---|
Keywords: | kbbug kbpending KB180348 |
---|
|