VBA：使用 Excel 查询访问权限。为何这么慢？

VBA: Querying Access with Excel. Why so slow?

提问by Dan

I found this code online to query Access and input the data into excel (2003), but it is much slower than it should be:

我在网上找到了这段代码，用于查询 Access 并将数据输入到 excel (2003) 中，但它比应有的速度慢得多：

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.

问题是这段代码需要很长时间。如果我打开 Access 并在其中运行查询，则大约需要 1/10 的时间。有没有办法加快这个速度？或者这可能需要这么长时间的任何原因？我所有的查询都是简单的选择查询，带有简单的 where 语句并且没有连接。即使是一个select * from [test]查询也比它应该花费的时间要长得多。

EDIT: I should specify that the line

编辑：我应该指定该行

Range(CellPaste).CopyFromRecordset RST

Range(CellPaste).CopyFromRecordset RST

was the one taking a long time.

是一个花了很长时间。

采纳答案by Dan

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.

这应该会加快速度，但仍然不能解决根本问题。

回答by Ryan Shannon

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

我不是专家，但我运行的代码几乎完全相同，结果很好。一个区别是我使用Command对象以及Connection对象。你在哪里

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? :-)

我不知道这是否或为什么会有所帮助，但也许会？:-)

回答by onedaywhen

I don't think you are comparing like-with-like.

我不认为你在比较同类。

In Access, when you view a Query's dataview what happens is:

在 Access 中，当您查看查询的数据视图时，会发生以下情况：

• an existing open connection is used (and kept open);
• a recordset is partially filled with the first few rows only (and kept open);
• the partial resultset is shown in a grid dedicated to the task and optimized for the native data access method Access employs (direct use of the Access Database Engine DLLs, probably).

• 使用现有的开放连接（并保持开放）；
• 记录集仅部分填充前几行（并保持打开状态）；
• 部分结果集显示在专用于该任务的网格中，并针对 Access 使用的本机数据访问方法进行了优化（可能直接使用 Access 数据库引擎 DLL）。

In your VBA code:

在您的 VBA 代码中：

• a new connection is opened (then later closed and released);
• the recordset is fully populated using all rows (then later closed and released);
• the entire resultset is read into a Excel's generic UI using non-native data access components.

• 打开一个新连接（然后关闭并释放）；
• 使用所有行完全填充记录集（然后关闭并释放）；
• 使用非本地数据访问组件将整个结果集读入 Excel 的通用 UI。

I think the most significant point there is that the dataview in Access doesn't fetch the entire resultset until you ask it to, usually by navigating to the last row in the resultset. ADO will always fetch all rows in the resultset.

我认为最重要的一点是 Access 中的数据视图在您要求之前不会获取整个结果集，通常是导航到结果集中的最后一行。ADO 将始终获取结果集中的所有行。

Second most significant would be the time taken to read the fetched rows (assuming a full resultset) into the UI element and the fact Excel's isn't optimized for the job.

第二重要的是将提取的行（假设是完整的结果集）读入 UI 元素所花费的时间，而且 Excel 并未针对作业进行优化。

Opening, closing and releasing connections and recordsets should be insignificant but are still a factor.

打开、关闭和释放连接和记录集应该无关紧要，但仍然是一个因素。

I think you need to do some timings on each step of the process to find the bottleneck. When comparing to Access, ensure you are getting a full resultset e.g. check the number of rows returned.

我认为您需要对流程的每个步骤进行一些计时以找到瓶颈。与 Access 进行比较时，请确保获得完整的结果集，例如检查返回的行数。

回答by manji

I would recommend you to create the Recordsetexplicitly rather than implicitly using the Executemethod. When creating explicitly you can set its CursorType and LockType properties which have impact on performance.

我建议您Recordset使用该Execute方法显式创建而不是隐式 创建。显式创建时，您可以设置对性能有影响的 CursorType 和 LockType 属性。

From what I see, you're loading data in Excel, then closing the recordset. You don't need to update, count records, etc... So my advice would be to create a Recordsetwith CursorType = adOpenForwardOnly & LockType = adLockReadOnly:

据我所知，您正在 Excel 中加载数据，然后关闭记录集。您不需要更新、计算记录等...所以我的建议是创建一个Recordsetwith CursorType = adOpenForwardOnly & LockType = adLockReadOnly：

...
RST.Open SQLQuery, Con, adOpenForwardOnly, adLockReadOnly
Range(CellPaste).CopyFromRecordset RST
...

Recordset Object (ADO)

Recordset Object (ADO)

回答by JimmyPena

Since you're using Access 2003, use DAO instead, it will be faster with the Jet engine.

由于您使用的是 Access 2003，请改用 DAO，使用 Jet 引擎会更快。

See http://www.erlandsendata.no/english/index.php?d=envbadacexportdaofor sample code.

有关示例代码，请参阅http://www.erlandsendata.no/english/index.php?d=envbadacexportdao。

Note that you should never use the "As New" keyword, as it will lead to unexpected results.

请注意，永远不要使用“As New”关键字，因为它会导致意外结果。

回答by Lunatik

I used your code and pulled in a table of 38 columns and 63780 rows in less than 7 seconds - about what I'd expect - and smaller recordsets completed almost instantaneously.

我使用了您的代码并在不到 7 秒的时间内拉入了一个包含 38 列和 63780 行的表格——这与我的预期差不多——并且较小的记录集几乎立即完成。

Is this the kind of performance you are experiencing? If so, it is consistent with what I'd expect with an ADO connection from Excel to an MDB back-end.

这是您正在体验的表演吗？如果是这样，它与我对从 Excel 到 MDB 后端的 ADO 连接的期望一致。

If you are seeing much slower performance than this then there must be some local environment conditions that are affecting things.

如果您看到的性能比这慢得多，那么一定有一些影响事物的本地环境条件。

回答by Thorsten

If you retrieve a lot of records, it would explain why the Range(CellPaste)takes so long. (If you execute the query in Access it wouldn't retrieve all the records, but if you do the CopyFromRecordset it requires all the records.)

如果您检索了大量记录，这将解释为什么Range(CellPaste)需要这么长时间。（如果您在 Access 中执行查询，它不会检索所有记录，但如果您执行 CopyFromRecordset，它需要所有记录。）

There is a MaxRows parameter for CopyFromRecordset:

CopyFromRecordset 有一个 MaxRows 参数：

Public Function CopyFromRecordset ( _
    Data As Object, _
    <OptionalAttribute> MaxRows As Object, _
    <OptionalAttribute> MaxColumns As Object _
) As Integer

Try if settings this to a low value (like 10 or so) changes the performance.

尝试将其设置为较低的值（如 10 左右）是否会改变性能。

回答by Philippe Grondier

What about the following turnarounds or improvements:

以下周转或改进如何：

1. Once opened, save the recordset as xml file (rst.saveToFile xxx) and then have Excel reopen it.
2. Once opened, put recordset data in an array (rst.getRows xxx), and copy the array on the active sheet
3. And, at any time, minimise all memory / access requirements: open the recordset as read-only, forward only, close the connection once the data is on your side, etc.

1. 打开后，将记录集保存为 xml 文件 (rst.saveToFile xxx)，然后让 Excel 重新打开它。
2. 打开后，将记录集数据放入一个数组（rst.getRows xxx）中，并将该数组复制到活动表上
3. 并且，在任何时候，最小化所有内存/访问要求：以只读方式、仅转发方式打开记录集，一旦数据在您身边就关闭连接等。

回答by RIF

I don't know if it will help, but I am using VBA and ADO to connect to an Excel spreadsheet.

我不知道它是否会有所帮助，但我正在使用 VBA 和 ADO 连接到 Excel 电子表格。

It was retrieving records lightning-fast (<5 seconds), but then all of a sudden it was awfully slow (15 seconds to retrieve one record). This is what lead me to your post.

它正在以闪电般的速度检索记录（<5 秒），但突然间它非常慢（检索一条记录需要 15 秒）。这就是让我看到你的帖子的原因。

I realized I accidentally had the Excel file open myself (I had been editing it).

我意识到我不小心打开了 Excel 文件（我一直在编辑它）。

Once I closed it, all was lightening fast again.

一旦我关上它，一切又恢复了快速。

回答by SwiftJr

The problem 9 times out of 10 is to do with the Cursor Type/Location you are using.

10 次中有 9 次出现问题与您使用的光标类型/位置有关。

Using dynamic cursors over network connections can slow down the retrieval of data, even if the query executed very fast.

在网络连接上使用动态游标会减慢数据的检索速度，即使查询执行得非常快。

IF you want to get large amounts of data very quickly, you'll need to use CursorLocation = adUseClient on your connection. This mean's you'll only have a static local cursor, so you won't get live updated from other users.

如果您想快速获取大量数据，则需要在连接上使用 CursorLocation = adUseClient。这意味着您将只有一个静态的本地游标，因此您不会从其他用户那里获得实时更新。

However - if you are only reading data, you'll save ADO going back to the DB for each individual record to check for changes.

但是 - 如果您只是读取数据，您将保存 ADO 返回到每个单独记录的数据库以检查更改。

I recently changed this as I had a simple loop, populating a list item, and each loop was taking around 0.3s. Not to slow, but even on 1,000 records thats 30 seconds! Changing only the cursor location let the entire process complete in under 1 second.

我最近改变了这个，因为我有一个简单的循环，填充一个列表项，每个循环大约需要 0.3 秒。不慢，但即使是 1000 条记录，也就是 30 秒！仅更改光标位置可让整个过程在 1 秒内完成。