VBA: Querying Access with Excel. Why so slow?
ado, excel, jet, vba
Solution
Lots of formulas may reference the query. Try temporarially turning on manual calculate in the macro and turning it off when all of your queries are done updating.
This should speed it up a bit, but still doesn't fix the underlying problem.
Problem
I found this code online to query Access and input the data into excel (2003), but it is much slower than it should be: ``` Sub DataPull(SQLQuery, CellPaste) Dim Con As New ADODB.Connection Dim RST As New ADODB.Recordset Dim DBlocation As String, DBName As String Dim ContractingQuery As String If SQLQuery = "" Then Else DBName = Range("DBName") If Right(DBName, 4) <> ".mdb" Then DBName = DBName + ".mdb" DBlocation = ActiveWorkbook.Path If Right(DBlocation, 1) <> "\" Then DBlocation = DBlocation + "\" Con.ConnectionString = DBlocation + DBName Con.Provider = "Microsoft.Jet.OLEDB.4.0" Con.Open Set RST = Con.Execute(SQLQuery) Range(CellPaste).CopyFromRecordset RST Con.Close End If End Sub ``` The problem is that this code takes very long. If I open up Access and just run the query in there it takes about 1/10th the time. Is there anyway to speed this up? Or any reason this might be taking so long? All my queries are simple select queries with simple where statements and no joins. Even a `select * from [test]` query takes much longer than it should. EDIT: I should specify that the line `Range(CellPaste).CopyFromRecordset RST` was the one taking a long time.