小编典典

在MS Access中回滚多个SQL更新查询

sql

我在MS Access的VBA中执行了多个SQL存储过程(例如UPDATE,SELECT INTO语句):

CurrentDb.Execute“ qry1”
CurrentDb.Execute“ qry2”

我希望这样做:
如果qry2失败,它将撤消qry1。
qry1和qry2是同时执行的(因为我有许多存储过程是在链中执行的),因此该过程运行得更快。

如何才能做到这一点?


阅读 180

收藏
2021-03-10

共1个答案

小编典典

事务可能适合,但它们允许回滚:http :
//msdn.microsoft.com/en-
us/library/bb243155.aspx

编辑

这是DAO中的一个粗略示例:

Dim strSQL As String
Dim db As DAO.Database
Dim wrk As Workspace

On Error GoTo TrapError

    Set db = CurrentDb
    Set wrk = DBEngine.Workspaces(0)

    wrk.BeginTrans
        strSQL = "Update sysInfo Set InvoiceOR=False"
        db.Execute strSQL, dbFailOnError
    wrk.CommitTrans

Exit_Sub:
    Set db = Nothing
    Set wrk = Nothing
    Exit Sub

TrapError:

    MsgBox "Failed: " & Err.Description
    wrk.Rollback
    Err.Clear
    Resume Exit_Sub

以下是ADO的一些简要说明:

Dim cmd As ADODB.Command
Dim cn As ADODB.Connection

Set cmd = CreateObject("ADODB.Command")
Set cn = CurrentProject.Connection

cmd.CommandText = "Update sysInfo Set InvoiceOR=False"
cmd.ActiveConnection = cn
cmd.ActiveConnection.BeginTrans
cmd.Execute , , adExecuteNoRecords

If Err <> 0 Then
    cmd.ActiveConnection.RollbackTrans
Else
    cmd.ActiveConnection.CommitTrans
End If
2021-03-10