VBA: Querying Access with Excel. Why so slow?

Question

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.

Answer 1

I'm no expert, but I run almost exactly the same code with good results. One difference is that I use the Command object as well as the Connection object. Where you

Set RST = Con.Execute(SQLQuery)

I

Dim cmd As ADODB.Command
Set cmd.ActiveConnection = con
cmd.CommandText = SQLQuery
Set RST = cmd.Execute

I don't know if or why that might help, but maybe it will? :-)