我在MS Access的VBA中执行了多个SQL存储过程(例如UPDATE,SELECT INTO语句):
CurrentDb.Execute“ qry1” CurrentDb.Execute“ qry2”
我希望这样做: 如果qry2失败,它将撤消qry1。 qry1和qry2是同时执行的(因为我有许多存储过程是在链中执行的),因此该过程运行得更快。
如何才能做到这一点?
事务可能适合,但它们允许回滚: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