CODEWITHSUNDEEP
exceldatabasevbams-accessadodb
Jimmy
Jimmy

Reputation: 12487

Excel VBA ADODB Access Database Lookup - Reduce Query Time

I've written some VBA code which queries an access database and pulls that code into the cells. It works, but is extremely slow, mostly down to the way I've written it, however I'm struggling to speed it up.

The first and second query are the same, except the second query has a 1 month offset.

How can I speed this up?

First issue is that I seem to have to connect and close the database each loop. If I try and run both queries within the same database connection I get an error.

Second issue is the join seems to be quite slow. 

  ' 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

Edit

On request this is the db object

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

Upvotes: 1

Views: 487

Answers (1)

Erik A
Erik A

Reputation: 32652

I suggest only using adOpenKeyset, adLockOptimistic when you need it.

Locking and latching incurs overhead. If you're only going to read, use adOpenStatic, adLockReadOnly. This doesn't lock the records, and doesn't require a continuous connection to receive updates when records get deleted.

Your entire database object seems weird to me. You're declaring the recordset and connection with events, but not listening to any events. This all incurs a bit of overhead.

You can also disconnect the recordset, but if you're using a static-type recordset with a read-only lock that'll likely not make much difference.

Upvotes: 1

Related Questions