How to Pass an Access Form Value to a SQL Server Stored Procedure
Question details
The user needs to execute a SQL Server stored procedure from Microsoft Access by passing a value selected in a form combo box to a pass-through query.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Executing a backend SQL Server stored procedure using dynamic parameter values supplied directly from an Access form.
- Observed behavior
- Referencing the Access form directly in the pass-through query fails because SQL Server cannot evaluate Access form objects natively.
Ensure you have a working ODBC connection to your SQL Server database and that the pass-through query has the correct ODBC connect string configured.
Construct the Pass-Through SQL using VBA
Dynamically build the SQL Server pass-through query string in Access VBA by capturing the form value and updating the QueryDef object before execution.
Because SQL Server evaluates pass-through queries independently of Access, it cannot read direct form references like 'Forms!MyForm!cbxSKUFinder'. Any attempt to do so will result in an error. To bypass this limitation, you must capture the value using VBA and concatenate it directly into the SQL execution string.
Open the VBA Editor (ALT + F11), insert a new module, and create a public function that accepts the parameter value: `Public Function RunStoredProcedure(ByVal strParameterValue As String)`.
Inside the function, declare a DAO.QueryDef variable pointing to your pass-through query. Update its SQL property to include the execution command and parameter: `CurrentDb.QueryDefs("YourPassThroughQuery").SQL = "EXEC usp_GetWhereUsed @SKU = '" & Replace(strParameterValue, "'", "''") & "'"`.
If the stored procedure modifies data without returning records, use the `.Execute` method on the QueryDef. If it returns records, open a Recordset based on the modified pass-through query or simply open a linked Access form/report that uses this query as its record source.
Go to your Access form's Design View. In the combo box's AfterUpdate event or a command button's OnClick event, call the function by passing the control's value: `Call RunStoredProcedure(Me.cbxSKUFinder)`.
Boost Your Daily Productivity with WPS Office
While Microsoft Access is a specialized tool for database development, your everyday tasks require robust word processing, spreadsheet analysis, and presentations. WPS Office offers a completely free, lightweight, and highly compatible alternative to Microsoft Office for all your standard documentation needs.
- 1. Download the installer: Visit the official WPS Office website and click 'Download WPS Office Free' to get the installer for Windows, Mac, or Linux.
- 2. Install the software: Run the downloaded executable file and follow the quick setup wizard to install the suite on your computer.
- 3. Open existing Office files: Launch WPS Office and open your existing .docx, .xlsx, or .pptx files directly with no formatting loss.
Frequently Asked Questions
Why does my pass-through query throw an error when referencing an Access form?
Pass-through queries are sent exactly as they are written directly to the SQL Server database engine. Since SQL Server does not have access to your local computer's memory or Microsoft Access environment, it cannot resolve syntax like `[Forms]![FormName]![ControlName]`, causing it to throw a missing parameter or syntax error.
Is there a way to pass parameters to SQL Server without concatenating SQL strings?
Yes. Instead of using DAO pass-through queries, you can use ADO (ActiveX Data Objects) in VBA. By creating an ADODB.Command object, you can set its CommandType to adCmdStoredProc and append ADODB.Parameter objects. This safely passes values directly without vulnerable string concatenation.
How do I handle passing numeric values instead of text in the VBA SQL string?
When appending numeric values to your SQL string in VBA, do not surround the parameter value with single quotes. For example: `qdef.SQL = "EXEC usp_UpdateCount @CountID = " & Me.txtCountValue`. Ensure you validate the form control to confirm it is actually numeric before executing the code.
