MS Access Forms - Passing Parameters to Queries Dynamically
How to Use MS Access Forms to Pass Values to Queries
This article describes how to dynamically change a SQL Passthrough query and a local Access query. A SQL Server View from the AdventureWorks database is used, but a local Access table may be used, too.
Creating the Data Source for the Form
Create a SQL view in the Adventure Works database. The code below is a view that pulls data about the employees in the Adventure Works database.
USE [AdventureWorks]
GO
CREATE VIEW [dbo].[vw_employees]
AS
SELECT HumanResources.Employee.EmployeeID,
Person.Contact.FirstName + CASE WHEN Len(IsNull(Person.Contact.MiddleName,''))>0 THEN ' ' + Person.Contact.MiddleName + ' ' ELSE '' END +
Person.Contact.LastName as EmployeeName,
Person.Address.AddressLine1,
Person.Address.AddressLine2, Person.Address.City,
Person.StateProvince.StateProvinceCode, Person.Address.PostalCode,
HumanResources.Department.Name as DeptName
FROM Person.Address
INNER JOIN
HumanResources.EmployeeAddress ON Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID
INNER JOIN
HumanResources.Employee ON HumanResources.EmployeeAddress.EmployeeID = HumanResources.Employee.EmployeeID
INNER JOIN
HumanResources.EmployeeDepartmentHistory ON
HumanResources.Employee.EmployeeID = HumanResources.EmployeeDepartmentHistory.EmployeeID
INNER JOIN
HumanResources.Department ON HumanResources.EmployeeDepartmentHistory.DepartmentID = HumanResources.Department.DepartmentID
INNER JOIN
Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID
INNER JOIN
Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID
A Select statement on the new view displays the following results.
Create a SQL passthrough query in Access that will connect to the SQL Server and the Adventure Works database. Save and name the Pass Through query SQL_EmployeeList.
Click on the Pass-Through button in the Ribbon.
Click on the Properties button and click on the ellipsis button next to the ODBC Connect Str property.
Select the Adventure Works data source. If not already created, click New and follow the Wizard to set up a new data source.
Create the Unbound MS Access Form
Create the unbound form in Access using a couple of labels, a textbox and a command button.
Add code to the button by double clicking it.
This code will be creating an Excel file of the values from the query using a FileSystemObject and DoCmd to export the data. To set the query dynamically, a QueryDef object is used.
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim strFile As String
Dim sFile As String
Dim strExt As String
Dim appExcel As Object, appexcel2 As Object
Dim wb As Object
Dim qdf As QueryDef
Set appExcel = GetObject("", "Excel.Application")
If appExcel Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
End If
sFile = "C:\temp\vw_Employees"
strExt = ".xls"
strFile = sFile & strExt
Dim fs As Object
Dim isthere As Boolean
Set fs = CreateObject("Scripting.FileSystemObject")
isthere = fs.FileExists(strFile)
If isthere = True Then
fs.DeleteFile strFile, True
End If
Set qdf = CurrentDb.QueryDefs("SQL_EmployeeList")
qdf.SQL = "SELECT * FROM vw_Employees WHERE [EmployeeID] in(" & Forms!frmquerytest!Text1 & ")"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"SQL_EmployeeList", strFile, True
appExcel.workbooks.Open (strFile)
appExcel.Visible = True
Set appExcel = Nothing
Set fs = Nothing
DoCmd.Close
Exit_cmdOK_Click:
Exit Sub
Err_cmdOK_Click:
MsgBox Err.Description
Resume Exit_cmdOK_Click
End Sub
Test the form by going into FormView and entering an employee ID.
Excel opens with the data for the employeeID entered.
Note that we used the SQL keyword "IN" instead of and = sign in building the query. We can also enter multiple Employee ID numbers using a comma to separate them.
Excel opens again with the data for the employeeIDs entered in the TextBox.
Using a Linked SQL View in MS Access
This may also be done using a local version of the view by linking to it in the Access database. To do so, select the External Data tab and ODBC Database.
Select the Link to the Data Source option.
Select the AdventureWorks DataSource.
Select the view from the list of tables.
Create a local query and save it as qryEmployeeList.
Make a couple of changes to the code behind the form by replacing the name of the SQL Passthrough query with the name of the local query.
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim strFile As String
Dim sFile As String
Dim strExt As String
Dim appExcel As Object, appexcel2 As Object
Dim wb As Object
Dim qdf As QueryDef
Set appExcel = GetObject("", "Excel.Application")
If appExcel Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
End If
sFile = "C:\temp\vw_Employees"
strExt = ".xls"
strFile = sFile & strExt
Dim fs As Object
Dim isthere As Boolean
Set fs = CreateObject("Scripting.FileSystemObject")
isthere = fs.FileExists(strFile)
If isthere = True Then
fs.DeleteFile strFile, True
End If
Set qdf = CurrentDb.QueryDefs("qryEmployeeList")
qdf.SQL = "SELECT * FROM vw_Employees WHERE [EmployeeID] in(" & Forms!frmquerytest!Text1 & ")"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryEmployeeList", strFile, True
appExcel.workbooks.Open (strFile)
appExcel.Visible = True
Set appExcel = Nothing
Set fs = Nothing
DoCmd.Close
Exit_cmdOK_Click:
Exit Sub
Err_cmdOK_Click:
MsgBox Err.Description
Resume Exit_cmdOK_Click
End Sub
Test the form again and notice that the results are the same.
Using QueryDef, it is easy to change query criteria in MS Access.