VBA：使用Excel查询访问。为何这么慢？ [英] VBA: Querying Access with Excel. Why so slow?

问题描述

我在网上找到了这段代码来查询Access并将数据输入到excel（2003）中，但是它比应该的要慢得多：

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

问题是此代码需要很长时间。如果我打开Access并在其中运行查询，则大约需要1/10的时间。反正有加速的吗？还是这可能需要很长时间？我所有的查询都是简单的select查询，带有简单的where语句，没有连接。甚至从[测试] 查询中选择<* code> select *也要花更长的时间。

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.

编辑：我应该指定行

范围（CellPaste）。CopyFromRecordsetRST

是花费很长时间的那个。

was the one taking a long time.

推荐答案

很多公式可能引用了该查询。尝试暂时打开宏中的手动计算，并在所有查询完成更新后将其关闭。

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.

这篇关于VBA：使用Excel查询访问。为何这么慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！