回滚MS Access中的多个SQL更新查询 [英] Rollback Multiple SQL update queries in MS Access

问题描述

我在MS Access中的VBA中执行了多个SQL存储过程（例如UPDATE，SELECT INTO语句）：

I have multiple SQL stored procedures (e.g. UPDATE, SELECT INTO statements) executed in VBA in MS Access:

CurrentDb.Executeqry1

CurrentDb.Executeqry2

CurrentDb.Execute "qry1"
CurrentDb.Execute "qry2"

我想要这样：

*如果qry2失败，它会撤消qry1。

* qry1和qry2同时执行，（因为我有很多这些存储过程在一个链中执行），所以程序运行得更快。

I want it so that:
* if qry2 fails, it will undo qry1.
* qry1 and qry2 are executed at the same time, (as I have many of these stored procedures executed in a chain), so the procedure runs faster.

推荐答案

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

Transactions may suit, they allow rollback: http://msdn.microsoft.com/en-us/library/bb243155.aspx

EDIT

这是DAO的一个粗略例子：

Here is a rough example in 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的一些粗略的注释：

Here are some rough notes for 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

这篇关于回滚MS Access中的多个SQL更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！