如何在 Microsoft Access 的不同上下文中使用 VBA 中的参数? [英] How do I use parameters in VBA in the different contexts in Microsoft Access?

问题描述

我从 bobby-tables.com.但是，我正在使用 Access 中的一个复杂应用程序，它有很多动态 SQL，在各种地方都带有字符串连接.

I've read a lot about SQL injection, and using parameters, from sources like bobby-tables.com. However, I'm working with a complex application in Access, that has a lot of dynamic SQL with string concatenation in all sorts of places.

我想更改以下内容并添加参数，以避免错误并允许我处理带有单引号的名称，例如 Jack O'Connel.

It has the following things I want to change, and add parameters to, to avoid errors and allow me to handle names with single quotes, like Jack O'Connel.

它使用:

• DoCmd.RunSQL 执行 SQL 命令
• DAO 记录集
• ADODB 记录集
• 表单和报告，使用 DoCmd.OpenForm 和 DoCmd.OpenReport 打开，在 WhereCondition 参数中使用字符串连接
• 域聚合，如使用字符串连接的 DLookUp

• DoCmd.RunSQL to execute SQL commands
• DAO recordsets
• ADODB recordsets
• Forms and reports, opened with DoCmd.OpenForm and DoCmd.OpenReport, using string concatenation in the WhereCondition argument
• Domain aggregates like DLookUp that use string concatenation

查询的结构主要是这样的:

The queries are mostly structured like this:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = " & Me.SomeTextbox

对于这些不同类型的查询，我有哪些选择可以使用参数?

What are my options to use parameters for these different kinds of queries?

_{这个问题旨在作为一个资源，对于频繁的我如何使用参数评论各种帖子}

_{This question is intended as a resource, for the frequent how do I use parameters comment on various posts}

推荐答案

有很多方法可以在查询中使用参数.我将尝试为其中的大多数提供示例，以及它们适用的地方.

There are many ways to use parameters in queries. I will try to provide examples for most of them, and where they are applicable.

首先，我们将讨论 Access 独有的解决方案，例如表单、报告和域聚合.然后，我们将讨论 DAO 和 ADO.

First, we'll discuss the solutions unique to Access, such as forms, reports and domain aggregates. Then, we'll talk about DAO and ADO.

在 Access 中，您可以在 SQL 代码中直接使用窗体和报表上控件的当前值.这限制了对参数的需求.

In Access, you can directly use the current value of controls on forms and reports in your SQL code. This limits the need for parameters.

您可以通过以下方式引用控件:

You can refer to controls in the following way:

Forms!MyForm!MyTextbox 用于表单上的简单控件

Forms!MyForm!MyTextbox for a simple control on a form

Forms!MyForm!MySubform.Form!MyTextbox 用于子窗体上的控件

Reports!MyReport!MyTextbox 用于报表控件

示例实现:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Forms!MyForm!MyTextbox" 'Inserts a single value
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = Forms!MyForm!MyTextbox" 'Inserts from a different table

这可用于以下用途:

使用 DoCmd.RunSQL 时，普通查询(在 GUI 中)、表单和报告记录源、表单和报告过滤器、域聚合、DoCmd.OpenForm 和 <代码>DoCmd.OpenReport

When using DoCmd.RunSQL, normal queries (in the GUI), form and report record sources, form and report filters, domain aggregates, DoCmd.OpenForm and DoCmd.OpenReport

这不能用于以下用途:

This is not available for the following uses:

使用 DAO 或 ADODB 执行查询时(例如打开记录集，CurrentDb.Execute)

When executing queries using DAO or ADODB (e.g. opening recordsets, CurrentDb.Execute)

Access 中的TempVars 是全局可用的变量，可以在VBA 中或使用宏进行设置.它们可以重复用于多个查询.

TempVars in Access are globally available variables, that can be set in VBA or using macro's. They can be reused for multiple queries.

示例实现:

TempVars!MyTempVar = Me.MyTextbox.Value 'Note: .Value is required
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = TempVars!MyTempVar"
TempVars.Remove "MyTempVar" 'Unset TempVar when you're done using it

TempVars 的可用性与表单和报告中的值相同:不可用于 ADO 和 DAO，可用于其他用途.

Availability for TempVars is identical to that of values from forms and reports: not available for ADO and DAO, available for other uses.

我推荐 TempVars 在打开表单或报告时使用参数而不是引用控件名称，因为如果打开它的对象关闭，TempVars 保持可用.我建议为每个表单或报告使用唯一的 TempVar 名称，以避免在刷新表单或报告时出现奇怪的现象.

I recommend TempVars for using parameters when opening forms or reports over referring to control names, since if the object opening it closes, the TempVars stay available. I recommend using unique TempVar names for every form or report, to avoid weirdness when refreshing forms or reports.

与 TempVars 非常相似，您可以使用自定义函数和静态变量来存储和检索值.

Much like TempVars, you can use a custom function and static variables to store and retrieve values.

示例实现:

Option Compare Database
Option Explicit

Private ThisDate As Date


Public Function GetThisDate() As Date
    If ThisDate = #12:00:00 AM# Then
        ' Set default value.
        ThisDate = Date
    End If 
    GetThisDate = ThisDate
End Function


Public Function SetThisDate(ByVal NewDate As Date) As Date
    ThisDate = NewDate
    SetThisDate = ThisDate
End Function

然后:

SetThisDate SomeDateValue ' Will store SomeDateValue in ThisDate.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeDateField] = GetThisDate()"

此外，可以创建带有可选参数的单个函数，用于设置和获取私有静态变量的值:

Also, a single function with an optional parameter may be created for both setting and getting the value of a private static variable:

Public Function ThisValue(Optional ByVal Value As Variant) As Variant
    Static CurrentValue As Variant
    ' Define default return value.
    Const DefaultValue  As Variant = Null

    If Not IsMissing(Value) Then
        ' Set value.
        CurrentValue = Value
    ElseIf IsEmpty(CurrentValue) Then
        ' Set default value
        CurrentValue = DefaultValue
    End If
    ' Return value.
    ThisValue = CurrentValue
End Function

要设置一个值:

ThisValue "Some text value"

获取值:

CurrentValue = ThisValue

在查询中:

ThisValue "SomeText"  ' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"

<小时>

使用 DoCmd.SetParameter

DoCmd.SetParameter 的用途相当有限，所以我会简短一些.它允许您设置一个参数以在 DoCmd.OpenForm、DoCmd.OpenReport 和其他一些 DoCmd 语句中使用，但它不起作用使用 DoCmd.RunSQL、过滤器、DAO 和 ADO.

---

Using DoCmd.SetParameter

The uses of DoCmd.SetParameter are rather limited, so I'll be brief. It allows you to set a parameter for use in DoCmd.OpenForm, DoCmd.OpenReport and some other DoCmd statements, but it doesn't work with DoCmd.RunSQL, filters, DAO and ADO.

示例实现

DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"

<小时>

使用 DAO

在 DAO 中，我们可以使用 DAO.QueryDef 对象来创建查询、设置参数，然后打开记录集或执行查询.您首先设置查询的 SQL，然后使用 QueryDef.Parameters 集合来设置参数.

---

Using DAO

In DAO, we can use the DAO.QueryDef object to create a query, set parameters, and then either open up a recordset or execute the query. You first set the queries' SQL, then use the QueryDef.Parameters collection to set the parameters.

在我的示例中，我将使用隐式参数类型.如果您想让它们明确，请添加 PARAMETERS 声明到您的查询中.

In my example, I'm going to use implicit parameter types. If you want to make them explicit, add a PARAMETERS declaration to your query.

示例实现

'Execute query, unnamed parameters
With CurrentDb.CreateQueryDef("", "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ?p1 And Field2 = ?p2")
    .Parameters(0) = Me.Field1
    .Parameters(1) = Me.Field2
    .Execute
End With

'Open recordset, named parameters
Dim rs As DAO.Recordset
With CurrentDb.CreateQueryDef("", "SELECT Field1 FROM Table2 WHERE Field1 = FirstParameter And Field2 = SecondParameter")
    .Parameters!FirstParameter = Me.Field1 'Bang notation
    .Parameters("SecondParameter").Value = Me.Field2 'More explicit notation
    Set rs = .OpenRecordset
End With

虽然这仅在 DAO 中可用，但您可以为 DAO 记录集设置许多内容以使其使用参数，例如表单记录集、列表框记录集和组合框记录集.但是，由于 Access 在排序和过滤时使用文本而不是记录集，如果您这样做，这些事情可能会出现问题.

While this is only available in DAO, you can set many things to DAO recordsets to make them use parameters, such as form recordsets, list box recordsets and combo box recordsets. However, since Access uses the text, and not the recordset, when sorting and filtering, those things may prove problematic if you do.

您可以通过使用 ADODB.Command 对象在 ADO 中使用参数.使用 Command.CreateParameter 创建参数，然后将它们附加到 Command.Parameters 集合中.

You can use parameters in ADO by using the ADODB.Command object. Use Command.CreateParameter to create parameters, and then append them to the Command.Parameters collection.

您可以使用 ADO 中的 .Parameters 集合来显式声明参数，或者将参数数组传递给 Command.Execute 方法以隐式传递参数.

You can use the .Parameters collection in ADO to explicitly declare parameters, or pass a parameter array to the Command.Execute method to implicitly pass parameters.

ADO 不支持命名参数.虽然您可以传递名称，但不会对其进行处理.

ADO does not support named parameters. While you can pass a name, it's not processed.

示例实现:

'Execute query, unnamed parameters
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?"
    .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) 'adVarWChar for text boxes that may contain unicode
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, 8, Me.Field2) 'adInteger for whole numbers (long or integer)
    .Execute
End With

'Open recordset, implicit parameters
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "SELECT Field1 FROM Table2 WHERE Field1 = @FirstParameter And Field2 = @SecondParameter"
     Set rs = .Execute(,Array(Me.Field1, Me.Field2))
End With

与打开 DAO 记录集的限制相同.虽然这种方式仅限于执行查询和打开记录集，但您可以在应用程序的其他地方使用这些记录集.

The same limitations as opening DAO recordsets apply. While this way is limited to executing queries and opening recordsets, you can use those recordsets elsewhere in your application.

这篇关于如何在 Microsoft Access 的不同上下文中使用 VBA 中的参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！