Converting ADODB Loop into DAO

Question

Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request.

Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long roughly 7 seconds per request.

 Public Sub BC_Data()

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim strCon, SQL As String
Dim ID As Integer

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...\Database.accdb;"

cn.Open strCon
    For i = 0 To n
        ID = Range("A2").Offset(i, 0).Value
        SQL = "SELECT [Sales] WHERE [ID] = " & ID & _
        " AND [Date] >= #" & [A1].Text & "# AND [Date] <= #" & _
        [B1].Text & "#;"
        rs.Open SQL, cn
        Range("C2").Offset(0, i).CopyFromRecordset rs
        rs.Close
    Next
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

The SQL works fine and is just selecting each transaction for a given product ID and time period.

The reason I'm using this sort of loop is that the we only want the data for ~20 of the products at a time and they aren't necessarily in a sequence so the IDs could be 1,2,4, 7, 200, 205, 654 etc.

The IDs are stored in Column A and then the request loops through and pastes each new column in Columns C onwards.

I have 2 questions:

1. Will using a DAO connection instead of ADODB speed up this process? If so, how would I go about replicating this in DAO?

2. Is the method I'm using to loop through the IDs and then request an efficient one? Is there a faster method, maybe using a "For each" loop instead?

Thank you!

Answer 1

DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once.

Sub test()

    Dim vaIds As Variant
    Dim sSql As String

    vaIds = Split("1 2 4 7 200 205 654", Space(1))

    sSql = "SELECT [Sales] WHERE [ID] In(" & Join(vaIds, ",") & ")" & _
        " AND [Date] >= #" & [A1].Text & "# AND [Date] <= #" & _
        [B1].Text & "#;"

    Debug.Print sSql

End Sub