在MS Access运行异步查询 [英] Running asynchronous query in MS Access

问题描述

我想异步运行的一些重型的查询，但无法弄清楚如何做到这一点的.mdb文件。

I'm trying to run some heavy queries asynchronously, but can't figure out how to do it for an .mdb file.

这是我迄今为止：

 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.

推荐答案

您第5（非空）行无所事事在你的榜样。这些行正试图打开一个连接，但正在处理的更新查询你以后的code未使用该连接。

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.

您应该知道，你正在使用ROUND（）函数可能不会返回你所期望的。在VBA中回合（）函数使用银行家舍入或半轮连的逻辑。两个回合（15.665,2）和圆（15.675,2）将返回15.67。

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.

您最后一行是试图从你的更新语句打开记录。更新语句不返回的记录，所以没有记录返回。如果它返回一个记录，你会想使用类似CurrentDB.OpenRecordset而不是Object.OpenRecordset。

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".

对于此查询异步运行，你可能无法得到你想要的东西。 MS访问将要处理该本地计算机上的查询。我不知道一个办法，如果它是使用JET（MS接入）数据库异步运行查询。我怀疑，即使有一个命令，通过您的VBA code异步运行的查询时，它仍然会在你的应用的上下文中运行，并陷入了下去。

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.

如果你试图让，而查询运行我建议更新移动到一个单独的进程，例如VBScript文件您的应用程序响应。

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.

保存下列code在一个文件中像UpdateActueel.vbs名称

Save the following code in a file with a name like 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

和在VBA code，使用以下行运行脚本。

And in your VBA code, use the following line to run the script.

Shell "wscript ""C:\<Path to file>\UpdateActueel.vbs"""

这篇关于在MS Access运行异步查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！