vba MS Access 准备好的语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6572448/
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
MS Access prepared statements
提问by David
Is it possible to execute a prepared statement in MS Access on a local table in VBA like this:
是否可以在 MS Access 中对 VBA 中的本地表执行准备好的语句，如下所示：
UPDATE part SET part_description=? WHERE part_id=?
If so how is it done?
如果是这样，它是如何完成的？
回答by HansUp
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Set db = CurrentDb
strSql = "UPDATE Month_Totals Set item_date = [which_date]" & _
" WHERE id = [which_id];"
Debug.Print strSql
Set qdf = db.CreateQueryDef(vbNullString, strSql)
With qdf
.Parameters("which_date").Value = Date()
.Parameters("which_id").Value = 1
.Execute dbFailOnError
End With
That example used a new, unsaved QueryDef. If you have a saved parameter query, you can use it instead by substituting this line for the CreateQueryDefline:
该示例使用了一个新的、未保存的QueryDef. 如果您有一个已保存的参数查询，则可以通过将以下行替换为以下CreateQueryDef行来使用它：
Set qdf = db.QueryDefs("YourQueryName")
Either way, you can then refer to individual parameters by their names as I did, or by their positions in the SQL statement ... so this will work same as above:
无论哪种方式，您都可以像我一样通过它们的名称或它们在 SQL 语句中的位置来引用各个参数......所以这将与上述相同：
.Parameters(0).Value = Date()
.Parameters(1).Value = 1
Additional notes:
补充说明：
.Valueis the default property for aParameter, so including it here is not strictly required. On the other hand, it doesn't hurt to be explicit.- As Gord noted below, you can use "Bang notation" with the parameter's name like
!which_id, which is more concise than.Parameters("which_id")
.Value是 a 的默认属性Parameter，因此不严格要求在此处包含它。另一方面，明确表达也没有坏处。- 正如戈德在下面指出的，您可以使用带有参数名称的“Bang notation”，例如
!which_id，这比.Parameters("which_id")
