How do I use parameters in VBA in the different contexts in Microsoft Access?
There are many ways to use parameters in queries. I will try to provide examples for most of them, and where they are applicable.
First, we'll discuss the solutions unique to Access, such as forms, reports and domain aggregates. Then, we'll talk about DAO and ADO.
Using values from forms and reports as parameters
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 for a simple control on a form
Forms!MyForm!MySubform.Form!MyTextbox for a control on a subform
Reports!MyReport!MyTextbox for a control on a report
Sample implementation:
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
This is available for the following uses:
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:
When executing queries using DAO or ADODB (e.g. opening recordsets, CurrentDb.Execute)
Using TempVars as parameters
TempVars in Access are globally available variables, that can be set in VBA or using macro's. They can be reused for multiple queries.
Sample implementation:
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
Availability for TempVars is identical to that of values from forms and reports: not available for ADO and DAO, available for other uses.
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.
Using custom functions (UDFs) as parameters
Much like TempVars, you can use a custom function and static variables to store and retrieve values.
Sample implementation:
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
and then:
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
To set a value:
ThisValue "Some text value"
To get the value:
CurrentValue = ThisValue
In a query:
ThisValue "SomeText" ' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"
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.
Sample implementation
DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"
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.
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.
Sample implementation
'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
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.
Using ADO
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.
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 does not support named parameters. While you can pass a name, it's not processed.
Sample implementation:
'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
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.
How to put a variable into SQL code in access
Create a query where you pass the Machine as parameter.
For example:
PARAMETERS [prmMachine] Text (255);
INSERT INTO Machines ([Machine])
SELECT [prmMachine] AS _Machine;
Then, call the query in VBA:
With CurrentDb().QueryDefs("YourQueryName")
.Parameters("[prmMachine]").Value = Machine_keuze
.Execute dbFailOnError
End With
Too Few Parameters in Access VBA but works in Query builder
Form- and report-based parameters are only available in the GUI context (queries run using the GUI, forms, reports, macros and DoCmd.RunSQL). You're probably executing this through CurrentDb, and need to use a querydef instead.
With CurrentDb.CreateQueryDef("", "SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field32, [_tbl_Structure].[Supervisor Emp Num], [_tbl_Structure].Supervisor FROM _tbl_Structure RIGHT JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5 WHERE ((([_tbl_Structure].Supervisor)=?));")
.Parameters(0) = [Forms]![frm_Manager_Stats_NEW]![Text279]
Set rs = .OpenRecordset
End With
You can learn more about the different types of parameters, and when to use which one, in this answer
MS Access: Query Reuse / Supplying Query Parameters Using VBA
For the most part, Jet (Access) is not subject to the same injection problems that other databases experience, so it may suit to write the SQL using VBA and either update a query with the new sql, or set the form's record source.
Very roughly:
sSQL = "SELECT f1, f2 FROM tbl WHERE f3 = '" & Me.txt3 & "'"
CurrentDB.QueryDefs("aquery").SQL = sSQL
Alternatively, you can use parameters:
Query:
PARAMETERS txtCompany Text(150);
SELECT <...>
WHERE Company = txtCompany
Code:
Set qdf = db.QueryDefs("QueryName")
qdf.Parameters!txtCompany = Trim(frm!txtCompany)
How to use a SQL with Parameters as a Combobox Row Source in MS Access/VBA
Unfortunately, you can't directly use parameters in a rowsource.
You can, however, use parameters in a querydef, use that to create a recordset, and use that recordset to fill a combobox, e.g.:
With CurrentDb.CreateQueryDef("", strSql)
.Parameters!CompanyID = compID
Set myCombobox.Recordset = .OpenRecordset
End With
You can also use either TempVars or form-based parameters in row sources, e.g.
TempVars!CompID = compID
strSql = "SELECT NAME, BUSINESS_NUMBER, FILE_ID FROM COMPANY WHERE COMPANY_ID = TempVars!CompID ORDER BY CREATION_DATE;"
See How do I use parameters in VBA in the different contexts in Microsoft Access? for more info.
Passing SQL parameters containing spaces or hyphens
As it is mentioned it is better to use parametrized queries, but I know - way to concatenate string is too pleasurable.
Try this:
strSourceSql = "SELECT " & strSourceFld & " FROM " & strSourceTbl & " WHERE [" & strSourceFld & "] = [" & strDataElm & "]"
if you pass the field reference
And this, if you pass the value itself
strSourceSql = "SELECT " & strSourceFld & " FROM " & strSourceTbl & " WHERE " & strSourceFld & " = " & "'" & strDataElm & "'"
How do I use parameters in VBA in the different contexts in Microsoft Access?
There are many ways to use parameters in queries. I will try to provide examples for most of them, and where they are applicable.
First, we'll discuss the solutions unique to Access, such as forms, reports and domain aggregates. Then, we'll talk about DAO and ADO.
Using values from forms and reports as parameters
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 for a simple control on a form
Forms!MyForm!MySubform.Form!MyTextbox for a control on a subform
Reports!MyReport!MyTextbox for a control on a report
Sample implementation:
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
This is available for the following uses:
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:
When executing queries using DAO or ADODB (e.g. opening recordsets, CurrentDb.Execute)
Using TempVars as parameters
TempVars in Access are globally available variables, that can be set in VBA or using macro's. They can be reused for multiple queries.
Sample implementation:
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
Availability for TempVars is identical to that of values from forms and reports: not available for ADO and DAO, available for other uses.
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.
Using custom functions (UDFs) as parameters
Much like TempVars, you can use a custom function and static variables to store and retrieve values.
Sample implementation:
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
and then:
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
To set a value:
ThisValue "Some text value"
To get the value:
CurrentValue = ThisValue
In a query:
ThisValue "SomeText" ' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"
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.
Sample implementation
DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"
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.
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.
Sample implementation
'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
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.
Using ADO
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.
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 does not support named parameters. While you can pass a name, it's not processed.
Sample implementation:
'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
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.
Related Topics
Simple Way to Transpose Columns and Rows in Sql
Why Does Oracle 9I Treat an Empty String as Null
Is the SQL Where Clause Short-Circuit Evaluated
MySQL Query Group by Day/Month/Year
How Rownum Works in Pagination Query
What's the Best Practice For Primary Keys in Tables
MySQL Update Column With Value from Another Table
Check If Value Exists in Postgres Array
Select Group of Rows That Match All Items in a List
How to Return Only the Date from a SQL Server Datetime Datatype
Calculate a Running Total in MySQL
How to Implement One-To-One, One-To-Many and Many-To-Many Relationships While Designing Tables
Table Naming Dilemma: Singular Vs. Plural Names
Inner Join VS Left Join Performance in SQL Server
Column Calculated from Another Column
How to Insert a Line Break in a SQL Server Varchar/Nvarchar String