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:
Will using a DAO connection instead of ADODB speed up this process? If so, how would I go about replicating this in DAO?
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?