Question about transaction

[deadfish]

Hi,

I have several sql statement(to insert, delete records in different tables) which are run by the docmd.runsql command. Is it possible to implement transaction to all statement?

Like this:

begin tran
docmd.runsql 'insert record to tableA
docmd.runsql 'delete record to tableB
...
end tran

if it is possible to do so, could anyone show me how the code should look like? thx!

 

[VBslammer]

I think you'll have to use ADO to implement transaction support. Here's a sample from one of my class modules:

Public Function AddParentToMotor(ByVal motorID As Long, ByVal parentID As String, _
                                 ByVal usage As ParentType, _
                                 Optional ByRef recsAffected As Long) As Boolean
On Error GoTo ErrHandler
  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim strSQL As String
  Dim blnInTrans As Boolean
  
  Set cnn = CurrentProject.Connection
  Set cmd = New ADODB.Command
  Set cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdText
  
  'format update sql for motor table
  strSQL = "UPDATE MotorList SET [MotorIdNo]='" & parentID & "', " & _
            "[MotorUse]=" & usage & " WHERE [MotorFileNo]=" & motorID
  
  cnn.BeginTrans
  blnInTrans = True '=================================
  
  cmd.CommandText = strSQL
  cmd.Execute recsAffected
  
  cnn.CommitTrans
  blnInTrans = False '================================
  
  AddParentToMotor = True ' no errors occurred

ExitHere:
  On Error Resume Next
  Set cmd = Nothing
  Set cnn = Nothing
  Exit Function
ErrHandler:
  If blnInTrans Then
    cnn.RollbackTrans
    Debug.Print "Transaction rolled back!"
  End If
  Call LogError("CMotorBoss", "AddParentToMotor( )", Err, Err.Description)
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas

 

[deadfish]

Thanks VBslammer..

But does it means that I can put all the sql statements in the strSQL variable? Because I have 3 sql statements, which cannot to be combined into one.....

 

[VBslammer]

My example only uses one update which really doesn't require a transaction, but you can do all your action queries inside the transaction, and still roll them back if needed:

  cnn.BeginTrans
  blnInTrans = True [green]'=================================[/green]
  
  cmd.CommandText = strSQL1
  cmd.Execute recsAffected

  cmd.CommandText = strSQL2
  cmd.Execute recsAffected

  cmd.CommandText = strSQL3
  cmd.Execute recsAffected
  
  cnn.CommitTrans
  blnInTrans = False [green]'================================[/green]

VBSlammer

Unemployed in Houston, Texas