ACC1x: Cannot Rollback in Transaction Without Closing Database

ID: Q93140


The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1


SYMPTOMS

A subroutine or a function contains a transaction process which calls a subroutine or function that dimensions and opens a database using the OpenDatabase method. When the subroutine or function exits, Microsoft Access implicitly closes the database (no Close method is called). When the subroutine or function attempts to execute the CommitTrans or Rollback statement, Microsoft Access displays the following message:


   Commit or Rollback without BeginTrans. 


CAUSE

When the called subroutine or function exits, Microsoft Access implicitly closes the database. Internally, the initial attempt to close the database fails because the database was opened in a transaction. Microsoft detects this error and performs an implicit transaction rollback, then it closes the database successfully.


RESOLUTION

To perform a Rollback in the called subroutine or function, you must first call the Close statement to close the open database.


STATUS

This behavior no longer occurs in Microsoft Access version 2.0.


MORE INFORMATION

Steps to Reproduce Behavior


  1. Create the following module.
    
          '************************************************************
          'Declarations section of the module.
          '************************************************************
    
          Option Explicit
    
          '============================================================
          'The following Sub procedure starts a transaction, calls
          'another sub procedure, then attempts to rollback.
          '============================================================
          Sub Test1
             BeginTrans
                Call Test2
             Rollback
          End Sub
    
          '============================================================
          'The following Sub procedure sets a Database variable to
          'the current database.
          '============================================================
          Sub Test2
             Dim MyDB as Database
             Set MyDB = CurrentDB()
             'the database is implicitly closed on exit
          End Sub 


  2. Compile the module.


  3. In the Immediate window, enter Test1. Microsoft Access generates the following message:
    
          Commit or Rollback without BeginTrans. 


Perform the following four steps to demonstrate the resolution to this problem.
  1. Perform Step 1 above.


  2. Edit the second function to add MyDB.Close, as follows.
    
          '============================================================
          'The following Sub procedure will set a Database variable to
          'the current database, and then close the database.
          '============================================================
          Sub Test2
             Dim MyDB as Database
             Set MyDB = CurrentDB()
             MyDB.Close
          End Sub 


  3. Compile the module.


  4. In the Immediate window enter Test1. The Rollback works correctly because the code explicitly closes the database opened in the transaction.


Keywords : kbprg MdlTran
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: March 12, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.