pauloflaherty
asked on
Rollback a RunSQL command in code
I am trying to write code that can commit or rollback a query run via Docmd.RunSQL. The following code doesn't throw up errors, but neither does it rollback the transaction.
Dim dbs As Database
Dim rstRecords As Recordset
Dim MyWorkspace As Workspace
Set MyWorkspace = DBEngine.Workspaces(0)
Set dbs = CurrentDb
On Error GoTo CancelMyTrans
MyWorkspace.BeginTrans
DoCmd.RunSQL "UPDATE tblNewTable SET tblNewTable.Company = ""Change"" ;", True
If MsgBox("Commit these records", vbYesNo) = vbYes Then
MyWorkspace.CommitTrans
Set MyWorkspace = Nothing
Exit Sub
End If
CancelMyTrans:
MyWorkspace.Rollback
Set MyWorkspace = Nothing
MsgBox "All transactions rolled back"
Dim dbs As Database
Dim rstRecords As Recordset
Dim MyWorkspace As Workspace
Set MyWorkspace = DBEngine.Workspaces(0)
Set dbs = CurrentDb
On Error GoTo CancelMyTrans
MyWorkspace.BeginTrans
DoCmd.RunSQL "UPDATE tblNewTable SET tblNewTable.Company = ""Change"" ;", True
If MsgBox("Commit these records", vbYesNo) = vbYes Then
MyWorkspace.CommitTrans
Set MyWorkspace = Nothing
Exit Sub
End If
CancelMyTrans:
MyWorkspace.Rollback
Set MyWorkspace = Nothing
MsgBox "All transactions rolled back"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Seems strange that it will not work with docmd.openquery or docmd.runsql, especially when the help file for runsql mentions an argument for using transactions - guess this is referring to something else.