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.Workspaces(0)
--


Dim ws As ADODB.Connection
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]

@MrDeej
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