access tempvars 宏_sql – 如何在Microsoft Access中的不同上下文中使用VBA中的参数？...

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

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

使用表单和报表中的值作为参数

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

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

Forms！MyForm！MyTextbox用于对表单进行简单控制

Forms！MyForm！MySubform.Form！MyTextbox用于控制子窗体

报告！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

这不适用于以下用途：

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

使用TempVars作为参数

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

示例实施：

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,可用于其他用途.

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

使用自定义函数(UDF)作为参数

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

示例实施：

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()"

此外,可以为设置和获取私有静态变量的值创建带有可选参数的单个函数：

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.

示例实施

DoCmd.SetParameter "MyParameter",Me.MyTextbox

DoCmd.OpenForm "MyForm","ID = MyParameter"

使用DAO

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

在我的例子中,我将使用隐式参数类型.如果要将它们显式化,请在查询中添加PARAMETERS declaration.

示例实施

'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在排序和过滤时使用文本而不是记录集,如果这样做,这些事情可能会有问题.

使用ADO

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

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

ADO不支持命名参数.虽然您可以传递名称,但它不会被处理.

示例实施：

'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,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记录集相同的限制适用.虽然这种方式仅限于执行查询和打开记录集,但您可以在应用程序的其他位置使用这些记录集.