PRB: Jet ODBC Driver Error 40002: Operation Invalid at This Time

MORE INFORMATION

The Microsoft Access ODBC driver does not preserve cursors across transaction boundaries. This means that open Resultsets must be closed before entering or exiting the scope of a transaction. This is a limitation in the design of the ODBC driver.

Steps to Reproduce Behavior

1. Use the ODBC Administrator in the Control Panel to add a DSN called NWIND for the Microsoft Access ODBC driver pointing to the Northwind sample database.
2. Create a standard EXE project in Visual Basic. Form1 is created by default.
3. Add a Project Reference to Microsoft Remote Data Object 2.0.
4. Add a CommandButton (Command1) to the default form.
5. Add the following code to the form:

         Option Explicit
   
         Private Sub Command1()
         Dim cn As rdoConnection, rs As rdoResultset, SQL As String
           Set cn = RDOEngine(0).OpenConnection("NWIND", rdDriverNoPrompt, _
                                                False)
           SQL = "SELECT * FROM Customers"
           Set rs = cn.OpenResultset(SQL, rdOpenKeyset, rdConcurRowVer)
           cn.BeginTrans
           rs.Edit
           rs!ContactTitle = "Vice President"
           rs.Update
           cn.CommitTrans
           rs.Close
           cn.Close
         End Sub
   
   						

6. Run the project and click the CommandButton. The error above will be displayed.
7. Move the "cn.BeginTrans" line before the "Set rs =" line, and move the "cn.CommitTrans" line after the "rs.Close" line. Re-run the code and it will complete without the error.

(c) Microsoft Corporation 1998. All Rights Reserved. Contributions by Malcolm Stewart, Microsoft Corporation