使用 Access 管理事务

• 下载源文件 - 46.1 KB

引言

此项目是一个用 VBA 编写的 Access 2003 插件。它允许用户编写和执行具有事务控制的后续 DML SQL 语句。当您希望使用影响多个表中多个记录的 SQL 查询来修改数据时，此工具非常有用。
最后，经过仔细检查后，您可以提交整个事务。Microsoft Access 记录的修改是通过在字段或行之间移动来完成的，而且通常无法回滚。此插件避免了这种行为。用户还可以在提交工作之前预览跨多个表的影响的更改。

背景

通常，修改查询不会产生预期的结果。在这种情况下，如果用户没有数据库备份副本，在尝试恢复初始状态时可能会遇到严重问题。我创建了一个 MDA Access 插件，它允许编写 SQL 语句（SELECT、INSERT、UPDATE、DELETE）并在事务中执行它们，该事务可以提交或丢弃。

使用代码

此插件是自注册的。要使用它，您只需

1. 将 .mda 文件复制到 msaccess.exe 所在 Microsoft ACCESS 目录中
2. 打开 Microsoft Access 2003 或更高版本
3. 转到工具 -> 插件 -> 插件管理器
4. 浏览 .mda 文件
5. 点击“确定”

您可以在“工具 -> 插件”菜单下看到已加载的“ImmediateSQL”插件。您可以使用内部 VBA 宏编辑器浏览和更改源代码。主窗口的顶部，如上图所示，有一个标签指示事务状态。右上角有一个按钮，显示最近执行的 15 条语句。您可以通过单击它们来重新调用它们。

您可以通过单击“执行”按钮或按 F5 来编写 DML 语句（SELECT、INSERT、UPDATE、DELETE）并执行它们。事务从第一次执行开始。要查看事务的运行情况，您需要使用大量 update 查询修改表，然后执行“select *”语句来查看记录更改。然后，您可以将其与从数据库窗口的表面板打开的原始表进行比较。

在您使用 DB 并提交事务之前，更改在原始表中或对其他用户是不可见的。它适用于所有类型的表，包括连接的 ODBC，并且事务也跨多个数据库工作。

关注点

视图管理非常简单。它是一个执行语句的有趣代码

Private Sub ExecuteStatement()
    If IsNull(Me.txtStmt.Value) Or Trim(Me.txtStmt.Value) = "" Then
        MsgBox "No statement to execute", vbExclamation
        Exit Sub
    End If

    If Not isInTransaction Then
        Conn.BeginTrans
        isInTransaction = True
    End If

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = c
    cmd.CommandText = Me.txtStmt.Value
    On Error GoTo Errore
    Dim r As ADODB.Recordset
    Set r = cmd.Execute(recno)
    On Error GoTo 0
    If r.State <> adStateClosed Then
        r.Close
        r.LockType = adLockOptimistic
        r.CursorLocation = adUseClient
        r.Open
        OpenTempTable r
    Else
        MsgBox "processed rows:" & recno
    End If

    InsertIntoStatements Me.txtStmt.Value

    UpdateDisplay
    Exit Sub

Errore:

    MsgBox "Error:" & Err.Number & vbCrLf & Err.Description, vbCritical
    UpdateDisplay

End Sub

连接在窗口加载时设置。然后使用 ADODB.Command 执行语句。事务通过 ADODB.Connection 进行管理。如果语句返回记录集，则通过调用 OpenTempTable 子例程显示结果。通过调用 InsertIntoStatements 子例程，执行的语句被注册到内部表“_Statements”中。

OpenTempTable 子例程很有趣，因为它使用了一些内部 Access 函数来创建一个临时窗体。该 sub 添加的文本框数量与记录集中的字段数量相同，然后将它们绑定到事务记录集。最后，记录显示为数据表，看起来像一个真正的 Access 表。

Private Sub OpenTempTable(r As ADODB.Recordset)

    Dim frm As Form
    Dim frmname As String

    frmname = "_tmpForm"

    'Make a copy of form2 which is used as a template
    On Error Resume Next
    DoCmd.Close acForm, frmname
    DoCmd.DeleteObject acForm, frmname
    On Error GoTo 0
    DoCmd.CopyObject , "_tmpForm", acForm, "Form2"

    'Open the _tmpForm in design mode to allow editing
    DoCmd.OpenForm frmname, acDesign, , , , acHidden

    'Add a bound text box for each fields  
    For Each f In r.Fields
        With CreateControl(frmname, acTextBox)
             ' set control properties
             .Name = f.Name
             .Properties("ControlSource") = f.Name
        End With
    Next

    DoCmd.OpenForm frmname, acFormDS
    Set Forms(frmname).Recordset = r
    Forms(frmname).Refresh

    DoCmd.Save acForm, frmname

End Sub

InsertIntoStatements 子例程展示了如何通过 CodeProject.AccessConnection 而不是 CurrentProject.Connection 或 CurrentProject.AccessConnection 来访问插件隐藏的表。首先，子例程检查语句是否与最后执行的语句不同。如果不同，您需要将语句插入内部表中。然后，如果语句超过 15 条，您需要删除较旧的语句。

Private Sub InsertIntoStatements(stmt As String)

    Dim r As ADODB.Recordset
    Set r = New ADODB.Recordset

    'Check that the statement has not already inserted
    r.Open "SELECT count(1) AS cntr FROM _Statements WHERE date = _
    (SELECT MAX(DATE) FROM _Statements) AND STATEMENT = """ & stmt _
    & """", CodeProject.AccessConnection 
    If r!cntr > 0 Then
        Exit Sub
    End If
    r.Close

    'Insert into statements
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.CommandText = "INSERT INTO _Statements VALUES _
                (Now(), """ & _stmt & """ )"
    cmd.ActiveConnection = CodeProject.AccessConnection 

    cmd.Execute

    'if there are more than 15 statements
    r.Open "SELECT count(1) AS cntr FROM _Statements", _
        CodeProject.AccessConnection 
    If r!cntr > 15 Then
        'delete the oldest
        cmd.CommandText = "DELETE FROM _Statements WHERE date = _
                (SELECT MIN(date) FROM _Statements)"
        cmd.ActiveConnection = CodeProject.AccessConnection 

        cmd.Execute
    End If

    'refresh the statements list
    Me.List9.Requery

End Sub

我认为这个工具可以进一步发展，以创建和自动化数据库脚本来升级、清理或移动数据。

历史

• 2007 年 4 月 12 日 - 发布原始版本
• 2007 年 5 月 23 日 - 首次文章更新