vba 在 MS Access 中运行异步查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4814294/
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
Running asynchronous query in MS Access
提问by skerit
I'm trying to run some heavy queries asynchronously, but can't figure out how to do it for an .mdb file.
我正在尝试异步运行一些繁重的查询，但无法弄清楚如何为 .mdb 文件执行此操作。
This is what I have so far:
这是我到目前为止：
Dim wrksp As Workspace, qdf As QueryDef, rst As Recordset
Dim cnn As Connection, strConnect As String
Set wrksp = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
strConnect = "ODBC;Driver={Microsoft Access Driver (*.mdb)};Database=F:\Databank\webshop_ingrid.mde;Uid=;Pwd=;"
Set cnn = wrksp.OpenConnection("", dbDriverNoPrompt, False, _
strConnect)
Dim rs As Recordset
Dim strQuery As String
strQuery = "UPDATE ((((((tblSkuActueel LEFT JOIN qryStockSkuMetLaatsteDatumSubQuery ON tblSkuActueel.sku = qryStockSkuMetLaatsteDatumSubQuery.sku) LEFT JOIN " & _
"qrySkuCapNieuwste ON tblSkuActueel.sku = qrySkuCapNieuwste.sku) LEFT JOIN qrySkuListNieuwste ON tblSkuActueel.sku = qrySkuListNieuwste.sku) LEFT JOIN " & _
"qrySkuPPDNieuwste ON tblSkuActueel.sku = qrySkuPPDNieuwste.sku) INNER JOIN qrySkuApexNieuwsteMetBtw ON tblSkuActueel.sku = qrySkuApexNieuwsteMetBtw.sku) " & _
"LEFT JOIN qrySkuSpecialNieuwsteDS ON tblSkuActueel.sku = qrySkuSpecialNieuwsteDS.sku) LEFT JOIN qrySkuSpecialNieuwsteNB ON tblSkuActueel.sku = " & _
"qrySkuSpecialNieuwsteNB.sku SET tblSkuActueel.stock = qryStockSkuMetLaatsteDatumSubQuery.aantal, tblSkuActueel.apex = qrySkuApexNieuwsteMetBtw.apex, " & _
"tblSkuActueel.cap = qrySkuCapNieuwste.cap, tblSkuActueel.listprice = qrySkuListNieuwste.listprice, tblSkuActueel.ppd = qrySkuPPDNieuwste.ppd, " & _
"tblSkuActueel.procent = qrySkuApexNieuwsteMetBtw.procent, tblSkuActueel.apin = Round(qrySkuApexNieuwsteMetBtw.apex*qrySkuApexNieuwsteMetBtw.procent,2), " & _
"tblSkuActueel.BtwId = qrySkuApexNieuwsteMetBtw.btwid, tblSkuActueel.specialpricenb = [qryskuspecialnieuwstenb].[specialprice], " & _
"tblSkuActueel.specialpriceds = [qryskuspecialnieuwsteds].[specialprice]"
Set rs = Object.OpenRecordset(strQuery, dbOpenDynaset, dbSeeChanges + dbRunAsync)
That won't even open a connection as the connection string seems to be wrong.
这甚至不会打开连接，因为连接字符串似乎是错误的。
回答by Zusukar
Your first 5 (non-blank) lines are doing nothing in your example. Those lines are attempting to open a connection, but your later code that is dealing with the update query is not using that connection.
您的前 5 行（非空白）在您的示例中没有任何作用。这些行试图打开一个连接，但您稍后处理更新查询的代码没有使用该连接。
You should be aware that the Round() function that you are using may not return what you expect. The Round() function in VBA uses "Bankers Rounding" or "round half to even" logic. Both Round(15.665,2) and Round(15.675,2) will return 15.67.
您应该知道您正在使用的 Round() 函数可能不会返回您期望的内容。VBA 中的 Round() 函数使用“Bankers Rounding”或“round half to even”逻辑。Round(15.665,2) 和 Round(15.675,2) 都将返回 15.67。
Your final line is attempting to open a recordset from your update statement. The update statement does not return records, so there is no recordset to return. If it were to return a recordset, you would want to use something like "CurrentDB.OpenRecordset" rather than "Object.OpenRecordset".
您的最后一行试图从您的更新语句中打开一个记录集。更新语句不返回记录，因此没有要返回的记录集。如果它要返回一个记录集，您可能需要使用“CurrentDB.OpenRecordset”之类的东西，而不是“Object.OpenRecordset”。
Regarding asynchronous running of this query, you may not be able to get what you want. MS Access is going to process the query on the local computer. I do not know of a way to run a query asynchronously if it is using a JET (MS Access) database. I suspect that even if there is a command to run the query asynchronously through your VBA code, it would still be running in the context of your application and bog it down.
关于这个查询的异步运行，你可能得不到你想要的。MS Access 将在本地计算机上处理查询。如果使用 JET (MS Access) 数据库，我不知道异步运行查询的方法。我怀疑即使有一个命令通过您的 VBA 代码异步运行查询，它仍然会在您的应用程序的上下文中运行并陷入困境。
If you are trying to let your application be responsive while the query is running I would suggest moving the update to a separate process, for example a VBScript file.
如果您试图让您的应用程序在查询运行时响应，我建议将更新移动到单独的进程，例如 VBScript 文件。
Save the following code in a file with a name like UpdateActueel.vbs
将以下代码保存在名为 UpdateActueel.vbs 的文件中
strQuery = "UPDATE ((((((tblSkuActueel LEFT JOIN qryStockSkuMetLaatsteDatumSubQuery ON tblSkuActueel.sku = qryStockSkuMetLaatsteDatumSubQuery.sku) LEFT JOIN " & _
"qrySkuCapNieuwste ON tblSkuActueel.sku = qrySkuCapNieuwste.sku) LEFT JOIN qrySkuListNieuwste ON tblSkuActueel.sku = qrySkuListNieuwste.sku) LEFT JOIN " & _
"qrySkuPPDNieuwste ON tblSkuActueel.sku = qrySkuPPDNieuwste.sku) INNER JOIN qrySkuApexNieuwsteMetBtw ON tblSkuActueel.sku = qrySkuApexNieuwsteMetBtw.sku) " & _
"LEFT JOIN qrySkuSpecialNieuwsteDS ON tblSkuActueel.sku = qrySkuSpecialNieuwsteDS.sku) LEFT JOIN qrySkuSpecialNieuwsteNB ON tblSkuActueel.sku = " & _
"qrySkuSpecialNieuwsteNB.sku SET tblSkuActueel.stock = qryStockSkuMetLaatsteDatumSubQuery.aantal, tblSkuActueel.apex = qrySkuApexNieuwsteMetBtw.apex, " & _
"tblSkuActueel.cap = qrySkuCapNieuwste.cap, tblSkuActueel.listprice = qrySkuListNieuwste.listprice, tblSkuActueel.ppd = qrySkuPPDNieuwste.ppd, " & _
"tblSkuActueel.procent = qrySkuApexNieuwsteMetBtw.procent, tblSkuActueel.apin = Round(qrySkuApexNieuwsteMetBtw.apex*qrySkuApexNieuwsteMetBtw.procent,2), " & _
"tblSkuActueel.BtwId = qrySkuApexNieuwsteMetBtw.btwid, tblSkuActueel.specialpricenb = [qryskuspecialnieuwstenb].[specialprice], " & _
"tblSkuActueel.specialpriceds = [qryskuspecialnieuwsteds].[specialprice]"
Set DB = GetObject("F:\Databank\webshop_ingrid.mde")
DB.Execute strQuery
And in your VBA code, use the following line to run the script.
在您的 VBA 代码中，使用以下行运行脚本。
Shell "wscript ""C:\<Path to file>\UpdateActueel.vbs"""
回答by Kevin Ross
I can speak about executing this things against a JET data source however I have used this against SQL server so it should work. The trick is to use ADO and fire the execute statement with the option adAsyncExecute. I don't have the exact code to hand but here is a rough version off the top of my head
我可以谈论对 JET 数据源执行这些事情，但是我已经将它用于 SQL 服务器，因此它应该可以工作。诀窍是使用 ADO 并使用选项 adAsyncExecute 触发执行语句。我手头没有确切的代码，但这是我头脑中的粗略版本
Dim DbCon as ADODB.Connection
Dim dbCmd as ADODB.Command
DbCon.ConnectionString=”Your connection string”
With dbCmd
.commandtype= adCmdText.commandtext=”Your long SQL update statement”
.ActiveConnection=dbcon.Execute , , adAsyncExecute
End With
Dim DbCon 作为 ADODB.Connection
Dim dbCmd 为 ADODB.Command
DbCon.ConnectionString="您的连接字符串"
使用 dbCmd
.commandtype= adCmdText.commandtext="你的长 SQL 更新语句"
.ActiveConnection=dbcon.Execute , , adAsyncExecute
结束于
