Excel VBA ADODB Access 数据库查找 - 减少查询时间

Question

我已经编写了一些 VBA 代码来查询 Access 数据库并将该代码提取到单元格中。它可以工作，但是速度非常慢，主要取决于我编写它的方式，但是我正在努力加快速度。

第一个和第二个查询是相同的，除了第二个查询有 1 个月的偏移量。

我怎样才能加快速度？

第一个问题是我似乎必须在每个循环中连接和关闭数据库。如果我尝试在同一个数据库连接中运行这两个查询，我会收到错误消息。

第二个问题是加入似乎很慢。

  ' FIRST MONTH QUERY
  db.Connect
     db.RunGetResults ("SELECT data.*, monthdata.VAL, monthdata.MONTHVAL, monthdata.GREEN, monthdata.RED, monthdata.RAG, monthdata.CREATOR FROM data LEFT JOIN monthdata ON data.UID = monthdata.DATAUID WHERE [UID] = '" & (IDcell) & "'  AND [MONTHVAL] = #" & Format(Range("multidate"), "mm/dd/yyyy") & "#")
    'Debug.Print db.Recordset.RecordCount

            irow = IDcell.Row
            Do Until db.Recordset.EOF
            icol = 2
            For Each ifield In db.Recordset.Fields
            Sheet3.Cells(irow, icol) = ifield.Value
                icol = icol + 1
                Next
            i = i + 1
           progress i

            db.Recordset.MoveNext
            If db.Recordset.EOF = True Then
            Else
            End If
            Loop
        End If

    Next
    db.Disconnect

   GoTo 69

   ' SECOND MONTH QUERY
             For Each IDcell In Rng
            If IDcell <> "" Then
            db.Connect
            'Application.Goto Reference:="month2"
            db.RunGetResults ("SELECT monthdata.VAL, monthdata.MONTHVAL, monthdata.GREEN, monthdata.RED, monthdata.RAG, monthdata.CREATOR FROM data LEFT JOIN monthdata ON data.UID = monthdata.DATAUID WHERE [UID] = '" & (IDcell) & "'  AND [MONTHVAL] = #" & Format((month2), "mm/dd/yyyy") & "#")

            'Debug.Print db.Recordset.RecordCount

            irow = IDcell.Row
            Do Until db.Recordset.EOF
            icol = 18
            For Each ifield In db.Recordset.Fields
            Sheet3.Cells(irow, icol) = ifield.Value
            icol = icol + 1
            Next
             i = i + 1
           progress i
            db.Recordset.MoveNext
            If db.Recordset.EOF = True Then
            Else
            End If
            Loop
        End If
    Next
    db.Disconnect

编辑

根据要求这是数据库对象

Public WithEvents Connection As ADODB.Connection
Public WithEvents Recordset As ADODB.Recordset
Public Command As New ADODB.Command
Public FilePath
Public Password

Public Function Connect()

    If Connection.State = 1 Then Disconnect

   AccessConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FilePath & "';Jet OLEDB:Database Password='" & Password & "';"

       Connection.ConnectionString = AccessConnect
       Connection.CursorLocation = adUseClient
       Connection.mode = adModeReadWrite
       Connection.Open

End Function

Public Function Disconnect()
    On Error Resume Next
    Connection.Close
    On Error GoTo 0
End Function

Public Function RunGetResults(qryString)

    sqlQuery = qryString
    Recordset.Open sqlQuery, Connection, adOpenKeyset, adLockOptimistic

End Function
Public Function Execute(qryString)

    Connection.Execute (qryString)

End Function

Public Function Esc(eString)
    eString = Replace(eString, "'", "''")
    Esc = eString
End Function

Private Sub Class_Initialize()
    Set Connection = New ADODB.Connection
    Set Recordset = New ADODB.Recordset

    Password = "xxxxx"
End Sub

Answer 1

我建议只在需要时使用 adOpenKeyset, adLockOptimistic。

锁定和闩锁会产生开销。如果您只想阅读，请使用 adOpenStatic, adLockReadOnly。这不会锁定记录，并且在记录被删除时不需要持续连接来接收更新。

我觉得你的整个数据库对象都很奇怪。您正在声明记录集和与事件的连接，但没有收听任何事件。这一切都会产生一些开销。

您也可以断开记录集，但如果您使用的是带有只读锁的静态类型记录集，那可能不会有太大区别。