when using excel vba ADODB recordset to get prior value, Why is getting data from recordset so slow?

Question

I have this query in Access. It works very fast and with excellent performance. But when I want to use this query in Excel using ADODB.RECORDSET, it takes about 15 minutes to copy the record set information via Range.CopyFromRecordset

SELECT 
    d.ShamsiDate,
    d.UnitNo,
    d.[Counter_MVH+],
   (SELECT max( d1.[Counter_MVH+] )                
    FROM tblData AS d1                     
    WHERE d1.ShamsiDate < d.ShamsiDate AND d1.UnitNo = d.UnitNo
    ) AS PriorValue
FROM tblData d;

code that used in vba :

Dim cn As Object, rs As Object, sq As String
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

sq = _
    "SELECT d.ShamsiDate, d.UnitNo, d.[Counter_MVH+], " & _
        "(SELECT MAX(d1.[Counter_MVH+]) " & _
        "FROM [Data$] d1 " & _
        "WHERE d1.UnitNo = d.UnitNo AND d1.ShamsiDate < d.ShamsiDate ) AS PriorValue " & _
        "FROM [Data$] d;"

cn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
rs.Open sq, cn, 3, 1

Sheet2.Range("A2").CopyFromRecordset rs

rs.Close
cn.Close

Thank you. I would appreciate it if you could help me.

how many records and fields are we talking? - You not only have the Query-Execution time (which is usually pretty fast), but you also have the pasting of all records and fields (which is pretty resource intensive) — Lord-JulianXLII, Jun 28 '23 at 07:31
So you are copying data within the same workbook? Why this method? Also, try setting [Application.Calculation = xlManual](https://stackoverflow.com/a/56172619/3527297). — Gustav, Jun 28 '23 at 07:34
Posting the [same question](https://stackoverflow.com/questions/76564799/very-slow-qury-in-excel-vba-using-adodb-recordset-to-get-prior-value) will not guarantee a different result. However, I've done some googling (which should've been your first go-to) and came upon this similar [SO question](https://stackoverflow.com/questions/1574213/vba-querying-access-with-excel-why-so-slow) where it's not quite the same (Chill60 mentioned: "You are not querying Access you are querying an Excel workbook") but trying to put calculation to manual could help. Especially since you're querying ThisWorkbook. — Notus_Panda, Jun 28 '23 at 07:34

when using excel vba ADODB recordset to get prior value, Why is getting data from recordset so slow?

0 Answers0