ws rollback dosent work

[MrDeej]

At least not the way i would think it should

Dim rst As New ADODB.Recordset
Dim ws As Workspace
Set ws = DBEngine.Workspaces(0)

With rst
ws.BeginTrans
    .Open "SELECT * from [test]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    .AddNew
    !test = "test2"
    .Update
    .Close
    
    If MsgBox("vil du avbryte?", vbYesNo, "logidose") = vbYes Then
        MsgBox "rollback"
        ws.Rollback
        Exit Sub
    End If
    
ws.CommitTrans

End With

Selecting yes in the msgbox dosent rollback. Why?

[MrDeej]

Found the problem

--
Dim ws As Workspace
Set ws = DBEngine.Worksp aces(0)
--


Dim ws As ADODB.Connectio n
Set ws as currentproject. connection

---


First bulk is for DAO and second is for ADO. I needed ADO ie it didnt work.
Now it works fine :=)

[MikeTheBike]

At least not the way i would think it should

Dim rst As New ADODB.Recordset
Dim ws As Workspace
Set ws = DBEngine.Workspaces(0)

With rst
ws.BeginTrans
    .Open "SELECT * from [test]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    .AddNew
    !test = "test2"
    .Update
    .Close
    
    If MsgBox("vil du avbryte?", vbYesNo, "logidose") = vbYes Then
        MsgBox "rollback"
        ws.Rollback
        Exit Sub
    End If
    
ws.CommitTrans

End With

Selecting yes in the msgbox dosent rollback. Why?

Hi

Not sure what the problem is (I tend to use ADO connections), but suggest this mod to the If statment

Dim rst As New ADODB.Recordset 
Dim ws As Workspace 
Set ws = DBEngine.Workspaces(0) 
  
With rst 
ws.BeginTrans 
    .Open "SELECT * from [test]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic 
    .AddNew 
    !test = "test2" 
    .Update 
    .Close 
  
    If MsgBox("vil du avbryte?", vbYesNo, "logidose") = vbYes Then 
        MsgBox "rollback" 
        ws.Rollback 
    Else
        ws.CommitTrans 
    End If 
  
End With

and see if that does it!?

MTB

[MrDeej]

Thanks, i already found the error.

This 2 only work for DAO:

Dim ws As Workspace  
Set ws = DBEngine.Workspaces(0)

New code wich works:

Dim rst As New ADODB.Recordset
Dim ws As ADODB.Connection
Set ws = CurrentProject.Connection
With rst

ws.BeginTrans
    .Open "SELECT * from [test]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    .AddNew
    !test = "test2"
    .Update
    .Close
    
    If test = True Then
        MsgBox "rollback"
        ws.RollbackTrans
        Exit Sub
    End If
    
ws.CommitTrans

End With

[ADezii]

1. DAO Transaction Processinig
   http://bytes.com/topic/access/insigh...rocessing-what
2. ADO Transaction Processing
   http://bytes.com/topic/access/insigh...ion-processing