Speed up query from table using ADO and SQL

Question

I have an Excel macro that reads data from another file using ADO and SQL. It runs fine but it takes too long

Here is the part of the code that connects to the other file.

With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Mode = adModeRead
    .CursorLocation = adUseClient
    .Properties("Data Source") = "W:\Store.xlsm"
    .Properties("Extended Properties") = "Excel 12.0 Macro;HDR=Yes;IMEX=1"
    .Open OpenForwardOnly, LockReadOnly, CmdText
End With

Set rs = conn.Execute("SELECT `main$`.`" & MasterField & "`  FROM `main$` `main$` WHERE `main$`.Num= '" & Hyperion & "'")

Master = rs.Fields(0).Value

Thank you!

How long does it take, and how long do you expect it to take? — Tim Williams, Oct 09 '18 at 22:25

score 0 · Answer 1 · answered Oct 09 '18 at 21:04

0

Consider using .xlsb copy of the file.

answered Oct 09 '18 at 21:04

Hassan Voyeau

3,383
4
22
24

1

.Thank you. Not making any difference. Only the file size changed. – Gonzalez Oct 09 '18 at 21:35
Another thing, see answer here about data types, headings and casting - https://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data/ – Hassan Voyeau Oct 09 '18 at 21:39
1

No difference either – Gonzalez Oct 10 '18 at 14:11

Speed up query from table using ADO and SQL

1 Answers1