CommitTrans does not work (1 Viewer)

[JPaulo]

Hi all;

why is it that the transaction does not work CommitTrans ???

Private Sub SubDeleteAllTables(DataX, DataY As Variant)
    On Error GoTo Parar_Err
    Dim NUm As Long, X As Integer, S As String
    Dim Ws As Workspace, db As Database
    Dim td As TableDef
    Dim rst As Recordset
   
       Ws.BeginTrans       'first transaction
NUm = 0
For Each td In CurrentDb.TableDefs
     If Mid(td.Name, 1, 4) <> "MSYS" Then
    Set rst = CurrentDb.OpenRecordset(td.Name, dbOpenTable)  'Abre as tabelas
    Set db = CurrentDb
    
    'Deletes data between datAxe and DataY
  
    Do While Not rst.EOF
      If rst!TData >= DataX And rst!TData <= DataY Then
        rst.Delete
    NUm = NUm + 1
     End If
            rst.MoveNext
    Loop
    
    End If
Next td
    ' run or cancel the transaction
    S = "Esta Operação Vai Eliminar Todos os Movimentos com Datas Entre: " & DataX & " 'e' " & DataY & " ?"
    X = MsgBox(S, 32 + 4 + 256, "Mensagem")
    If X = 6 Then  'sim
       Ws.CommitTrans       ' run the transaction
    Else
       Ws.Rollback          ' cancel the transaction
       Exit Sub
    End If
    If NUm = 0 Then
       S = "Não Havia Movimentos no Período Indicado."
    ElseIf NUm = 1 Then
       S = "Um Movimento foi Apagado."
  
    ElseIf NUm > 1 Then
       S = Trim$(Str$(NUm)) & " Movimentos foram apagados."
    Else
    End If
    
    ' Prompts qty of records were deleted
    MsgBox S, vbInformation, "Movimento"
    
Parar_Fim:
    rst.Close
    db.Close
    Exit Sub
Parar_Err:
    MsgBox err.Description
    Resume Parar_Fim
End Sub

 

[Guus2005]

You did not instantiate WS:

Dim WS as workspace

set WS = dbengine(0)

WS.begintrans

If that's not enough, make sure that your recordset is part of the workspace you instantiated

set rst = WS(0).recordset

Enjoy!

 

[JPaulo]

Work a charme;

thank you very much.