BeginTrans Rollback CommitTrans withing Access

[vbFace]

I am trying to set up a transaction within Access itself using VBA. So far I have tried the following:

' This line doesn't error, but will error on CurrentProject.Connection.Rollback / .CommitTrans
' saying no transaction is started
CurrentProject.Connection.BeginTrans

' These lines do not error but do not do anything!
DBEngine.BeginTrans
DBEngine.Rollback
DBEngine.CommitTrans
' and
DBEngine.Workspaces(0).BeginTrans
DBEngine.Workspaces(0).Rollback
DBEngine.Workspaces(0).CommitTrans

This isn't my code, the code is in a real function. These are just the commands that I put around my SQL code to try this.

I am using DoCmd.RunSql "<the sql>" to run the INSERT and DELETE queries.

So, does anyone know how to do this the right way?

This is my real code:

Private Function BackupData() As Boolean
 On Error GoTo ErrorTrap
 
 Dim X                  As Integer
 Dim Y                  As Integer
 Dim sPKList            As String
 Dim rPKList            As ADODB.Recordset
 Dim SQL                As String

    
    BackupData = False

    DBEngine.Workspaces(0).BeginTrans

    DoCmd.RunSQL "INSERT INTO ARCHIVE_MailMaster SELECT * FROM MailMaster WHERE Study = '" & aryStudyList(X) & "';"
    DoEvents
    DoCmd.RunSQL "DELETE * FROM MailMaster WHERE Study = '" & aryStudyList(X) & "';"
    DoEvents

    ' Force an error to test Rollback
    Debug.Print 1/0

    ' lots more code here to INSERT/DELETE from other tables

    DBEngine.Workspaces(0).CommitTrans

ExitPoint:
    Exit Function
    
ErrorTrap:
    DBEngine.Workspaces(0).Rollback
    BackupData = False
    Resume ExitPoint
        
End Function

 

[vbFace]

Well, I got help from another site:

Private Function BackupData() As Boolean
 
 Dim X                  As Integer
 Dim SQL                As String
 Dim ws                 As DAO.Workspace
 Dim db                 As DAO.Database
 
    Set ws = DBEngine.Workspaces(0)
    Set db = CurrentDb
    
    BackupData = False
    ' Loop through all studies
    For X = 0 To UBound(aryStudyList)
    
        ws.BeginTrans
        
        On Error Goto ErrorTrap
        
        SQL = "INSERT INTO ARCHIVE_MailMaster SELECT * FROM MailMaster WHERE Study = '" & aryStudyList(X) & "';"
        db.Execute SQL, dbFailOnError
        DoEvents
        
        SQL = "DELETE * FROM MailMaster WHERE Study = '" & aryStudyList(X) & "';"
        db.Execute SQL, dbFailOnError
        DoEvents
        
        ' More code for 12 more tables

        ws.CommitTrans
                
    Next X
    BackupData = True
    
ExitPoint:
    Set db = Nothing
    Set ws = Nothing
    Exit Function
    
ErrorTrap:
    ws.Rollback
    BackupData = False
    Resume ExitPoint
        
End Function

It works perfect for many studies, but one study (with more records in the 13 tables) gives me an error:

Run-time error 3035 System resource exceeded

I have searched Google for this error message with no helpful results.

Anyone know if there is a limit in Access for BeginTrans/Rollback/CommitTrans???

 

[modest]

This was a while ago, but in case anyone was ever wondering, yes there is a limit. Not on how many times you can use it, but on the number of locks. I believe that the initial lock parameter permits 9500 records being locked at a time per transaction.