MS Access 准备好的语句 [英] MS Access prepared statements

问题描述

是否可以在 MS Access 中对 VBA 中的本地表执行准备好的语句，如下所示:

Is it possible to execute a prepared statement in MS Access on a local table in VBA like this:

UPDATE part SET part_description=? WHERE part_id=?

如果有，是怎么做的?

推荐答案

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

该示例使用了一个新的、未保存的 QueryDef.如果您有一个保存的参数查询，您可以使用它来代替 CreateQueryDef 这一行:

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 CreateQueryDef line:

Set qdf = db.QueryDefs("YourQueryName")

无论哪种方式，您都可以像我一样通过它们的名称或它们在 SQL 语句中的位置来引用各个参数......所以这将与上述相同:

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:

.Parameters(0).Value = Date()
.Parameters(1).Value = 1

附加说明:

1. .Value 是 Parameter 的默认属性，因此并不严格要求在此处包含它.另一方面，明确表达也无妨.
2. 正如下面戈德所指出的，您可以使用带有参数名称的Bang 符号"，例如 !which_id，这比 .Parameters("which_id")

1. .Value is the default property for a Parameter, so including it here is not strictly required. On the other hand, it doesn't hurt to be explicit.
2. 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")

这篇关于MS Access 准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！