代码之家 › 专栏 › 技术社区 › Jimmy

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

adodb ms-access vba database excel

Jimmy · 技术社区 · 6 年前

我已经编写了一些VBA代码,用于查询access数据库并将代码拉入单元格。它的工作,但是非常缓慢,主要是因为我写它的方式,但我正在努力加快它。

我怎样才能加快速度?

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

  ' 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

编辑

根据请求,这是db对象

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

1 回复 | 直到 6 年前

Erik A 6 年前

我建议只使用 adOpenKeyset, adLockOptimistic 当你需要的时候。

锁定和锁定会导致开销。如果你只想读书,就用 adOpenStatic, adLockReadOnly . 这不会锁定记录,并且在记录被删除时不需要连续连接来接收更新。

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