时间戳

首页 > 解决方案 > 插入/更新/删除语句后 ADODb 连接刷新（Excel/Access 联动）

sql - 插入/更新/删除语句后 ADODb 连接刷新（Excel/Access 联动）

问题描述

我制作了一个 Excel 界面来与 Access 数据库进行交互。目标是多人可以在只读模式下打开 Excel 界面，但仍使用 ActiveX 控件同时对数据库进行操作。

我有 3 个按钮显示 3 个用户窗体：一个用于“插入条目”，一个用于“删除条目”，一个用于“修改条目”。在这些用户表单中，我们可以分别输入新条目的值，然后通过 VBA 代码检查其有效性、确认删除一行或修改用户在表格显示上选择的条目的值。数据输入+确认后，与Access db建立ADO连接，执行sql查询。

例如，这里是“删除”用户窗体的代码，其他的都建立在相同的模型上。

Private Sub CommandButton1_Click()

    Dim lineArr() As Variant
    
    lineArr = returnSelectedLine
    
    Call doSqlInsert(CStr(lineArr(1)))
    Unload Me
        
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub


Private Sub UserForm_Terminate()
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub


Private Sub UserForm_Activate()
    Dim lineArr() As Variant
    
    lineArr = returnSelectedLine
    
    Me.Label8.Caption = "Are you sure you want to delete the article number " & lineArr(1) & " ? This action is irreversible."

End Sub


Sub doSqlInsert(art As String)

    Dim cn As Object
    Dim strFile As String
    Dim strCon As String
    Dim quer As String

    
    
    strFile = ThisWorkbook.Path + "\etwDb.accdb"
        
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile
        
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strCon
    
    quer = "DELETE FROM Dvpt WHERE [Article] = " & art & ";"
    
    cn.Execute quer

    cn.Close
    
    Set cn = Nothing
    
End Sub

Function returnSelectedLine()

    Dim rw As Integer
    Dim res(0 To 7) As Variant
    Dim i As Integer
    
    
    
    rw = ActiveCell.Row
    
    With ThisWorkbook.Worksheets("Liste dvpt")
        For i = 0 To 7
            res(i) = .Cells(rw, i + 2)
        Next i
    End With
    
    returnSelectedLine = res
End Function

这运作良好，并正确影响数据库上的数据。但是，此数据库也使用 QueryTable 对象（连接处于读取模式）显示在 Excel 文件中，我尝试在每个用户窗体终止时刷新该对象（请参阅 参考资料Private Sub UserForm_Terminate），但这并不能始终如一地工作。有时，我需要再做一两个动作（或再刷新一两次）才能显示更改，有时它会立即出现。

指示：工作表被编程，因此只能选择一个唯一的单元格，并且只能在 QueryTable 区域中选择。如果用户尝试在表格之外选择某些内容，则选择会返回到表格，因此我可以Selection.ListObject.QueryTable.Refresh毫无歧义地使用。

ADO 连接是否有执行时间？我是否以错误的方式刷新了我的 QueryTable？是什么导致了这种延迟？

谢谢你的帮助

标签： sqlexcelvbams-access

解决方案

不是答案，而是一个想法，太大而无法评论。您可以使用连接的事件，因此创建一个类 clsCustomConnection。像这样

 Private WithEvents cn As ADODB.Connection

Public Sub Initialise(strConnectionString As String)
    Set cn = New ADODB.Connection
    cn.ConnectionString = strConnectionString
    cn.Open
End Sub

Public Sub Execute(strSQL As String)
    cn.Execute strSQL
End Sub

Private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    '   Refresh any query tables of interest, based on the Error/Status
End Sub

然后在一个模块中有这样的东西：

Sub Testing()

Dim cnCustom As clsCustomConnection
Dim strConnectionString As String
Dim strSQL As String

Set cnCustom = New clsCustomConnection

cnCustom.Initialise strConnectionString
cnCustom.Execute strSQL

End Sub

这将使用连接来告诉其余代码它的成功/失败，通过使用连接的事件。

推荐阅读