VBA 等待 DoCmd.RunSQL 或更好的查询 - Ms Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10932780/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Wait for DoCmd.RunSQL OR Better Query - Ms Access
提问by StuckAtWork
I have a query which uses an array of tables (stored as a string array) to loop through my tables and perform a Delete/Update set of queries. The queries are identical other than the table names, hence using a loop to iterate through using the table name as a variable.
我有一个查询，它使用一组表（存储为字符串数组）来循环我的表并执行一组删除/更新查询。除了表名之外，查询是相同的，因此使用循环来迭代使用表名作为变量。
The problem is, my Delete query locks the table and the Update query runs too quickly afterward; I get a "db is locked" error.
问题是，我的删除查询锁定了表，之后更新查询运行得太快了；我收到“数据库已锁定”错误。
I need either of two things:
我需要两件事之一：
- A way to tell VBA to "wait for previous command" OR
- A way to concatenate these queries into one (or two) queries: one to delete the database rows and another to import the new ones. With this I could just run the queries from a standard access query (which should allocate proper time, finish queries etc)
- 一种告诉 VBA“等待上一个命令”的方法或
- 一种将这些查询连接成一个（或两个）查询的方法：一个是删除数据库行，另一个是导入新行。有了这个，我可以从标准访问查询运行查询（应该分配适当的时间，完成查询等）
The only catch to this is that there are parent-child relations, so the parent table has to be updated before its children (currently accomplished through array ordering).
唯一的问题是存在父子关系，因此父表必须在其子表之前更新（目前通过数组排序完成）。
Here's the current code which (sometimes) produces the "DB locked/in use" message:
这是（有时）产生“数据库锁定/使用中”消息的当前代码：
For i = 0 To UBound(tables)
'Delete all data first
sql = "DELETE * FROM " & tables(i)
DoCmd.RunSQL sql
'Update all data second
sql = "INSERT INTO " & tables(i) & " IN """ & toDB & """ SELECT " & tables(i) & " .* FROM " & tables(i) & " IN """ & fromDB & """;"
DoCmd.RunSQL sql
Next
Should clarify: the queries take one backend's (fromDB) rows from identical tables and pushes it to another backend's (toDB) rows
应该澄清：查询fromDB从相同的表中获取一个后端的 ( ) 行并将其推送到另一个后端的 ( toDB) 行
EDIT: In response to the questions regarding INSERT INTO, my problem with that is if I add fields to the toDB, it will delete them if I overwrite. The reason I have to do this backdoor approach is because the database is still in development, but is also being used with select tables. Updates and feature improvements are done daily. I cannot use a simple split-backend either, because the other computer accessing the database is not always on the network (we have to manually sync it when it returns to the network), so I work on one backend and it works on another, identical(ish, minus my schema update) backend.
编辑：针对有关 的问题INSERT INTO，我的问题是如果我向 中添加字段toDB，如果我覆盖，它会删除它们。我必须采用这种后门方法的原因是数据库仍在开发中，但也正在与选择表一起使用。每天都会进行更新和功能改进。我也不能使用简单的拆分后端，因为访问数据库的另一台计算机并不总是在网络上（我们必须在它返回网络时手动同步它），所以我在一个后端工作，它在另一个后端工作，相同（ish，减去我的架构更新）后端。
回答by Conrad Frix
You can use ADO instead of DoCmd.RunSQL to execute your SQL synchronously.
您可以使用 ADO 而不是 DoCmd.RunSQL 来同步执行您的 SQL。
Dim cmd As ADODB.Command
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
For i = 0 To UBound(tables)
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdText
.ActiveConnection = cnn
.CommandText = "DELETE * FROM " & tables(i)
.Execute
End With
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdText
.ActiveConnection = cnn
.CommandText = "INSERT INTO " & tables(i) & " IN """ & toDB & """ SELECT " & tables(i) & " .* FROM " & tables(i) & " IN """ & fromDB & """;"
.Execute
End With
Next
You could also add cnn.BeginTransand cnn.CommitTransto make the two statements Atomic.
您还可以添加cnn.BeginTrans和cnn.CommitTrans使这两个语句成为原子。
回答by Doug Glancy
I'm no expert, but I don't think you need the DELETE part. SELECT INTO is the syntax for making a table. If the table already exists, it's overwritten.
我不是专家，但我认为您不需要 DELETE 部分。SELECT INTO 是制作表格的语法。如果该表已存在，则将其覆盖。
回答by SeanC
If I need to wait for something, I tend to use DoEventsto allow windows to process any other actions that have been put on hold.
From the help: "Yields execution so that the operating system can process other events."
如果我需要等待某事，我倾向于使用DoEvents允许窗口处理已被搁置的任何其他操作。
来自帮助：“产生执行，以便操作系统可以处理其他事件。”
Found this:.BeginTransthen.CommitTrans dbForceOSFlush
as a way to force updates to be written before continuing
发现这个：.BeginTrans然后.CommitTrans dbForceOSFlush
作为一种在继续之前强制写入更新的方法
