访问 VBA DoCmd.TransferSpreadsheet 运行速度太慢并导致运行时错误 [英] Access VBA DoCmd.TransferSpreadsheet runs too slow and causes Run-time error

问题描述

我在 Access 中的自定义类别中有一组查询，以及一些获取查询名称并运行它们的 VBA 代码，然后将每个查询的结果复制到 Excel 电子表格上的单独选项卡中.代码如下:

I have a group of queries in a custom category in Access, and a bit of VBA code that gets the query names and runs them, and then copies the results for each query to a separate tab on an Excel spreadsheet. Here's the code:

Private Sub Command0_Click()

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim timestamp As String
Dim path As String

'Create the Excel spreadsheet
timestamp = Format(Now(), "yyyyMMddhhmmss")

path = "C:\Users\username\Desktop\ValidationResults" & timestamp & ".xlsx"

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets("Sheet1")
oBook.SaveAs path
oExcel.Quit

'This gets a list of query names and puts it in a recordset
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("01 - GetValidationQueries")

'this runs each query and copies the results to a unique worksheet
Do While Not rs.EOF
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, rs("Name"), path, True, Left(rs("Name"), 2)
    rs.MoveNext
Loop

End Sub

01 - GetValidationQueries"查询在 MSysNavPaneGroupToObjects 和 MSysNavPaneObjectIDs 表中查找，以获取自定义类别中查询的名称.

The "01 - GetValidationQueries" query looks in the MSysNavPaneGroupToObjects and MSysNavPaneObjectIDs tables to get the names of the queries in a custom category.

问题是，当我单击表单按钮运行此代码时，在 DoCmd 行出现以下错误:

The problem is that when I click the form button to run this code, I get the following error on the DoCmd line:

Run-time error '3275':
Unexpected error from external database driver (1309).

如果我逐行浏览代码(每次按 F8)它运行良好，所以我猜 TransferSpreadsheet 过程太慢了.除了在该行之后暂停，还有没有更好的方法来完成这项工作?

If I step thru the code line by line (hitting F8 each time) it runs fine, so I'm guessing the TransferSpreadsheet process is too slow. Other than putting a pause after that line, is there a better way of making this work?

使用以下代码(至少现在是这样)

using the following code (for now, at least)

Public Function fnWait(intNrOfSeconds As Integer)
Dim varStart As Variant
  varStart = Timer
  Do While Timer < varStart + intNrOfSeconds
  Loop
End Function

然后在 DoCmd 行之前和之后，我有...

Then before and after the DoCmd line, I have...

fnWait (2)

...创建一个 2 秒的延迟.到目前为止似乎工作正常.

...to create a 2 second delay. It seems to be working so far.

推荐答案

希望对您有所帮助.

http://p2p.wrox.com/access-vba/24421-wait-function-vba.html

我确实将它添加到了我的数据库中，因此如果您需要帮助实现它，请直接询问.

I did add it to my database so if you need help implementing it just ask.

这篇关于访问 VBA DoCmd.TransferSpreadsheet 运行速度太慢并导致运行时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！