Categories
Microsoft Access

Execute a Stored Procedure from Access

Create a pass through query in Access. Its content should call the sp like this:

Exec spCusInfo 65

The stored procedure will usually need a parameter, in the example, 65 could be the CusID. If sending a string, surround the parameter in single quotes. If there is more than 1 parameter, separate them with commas.

If you execute the query, you will always get the data for CusID=65. So in a button click event or an AfterUpdate event somewhere add VBA code like this:

Dim qry As QueryDef

Set qry = CurrentDb.QueryDefs("qrySpCusInfo")

qry.SQL = "exec spCusInfo " & Nz(Me.txtCusID, 0)

Me.Requery

See also https://oakdome.com/programming/MSAccess_StoredProcedureDataSource.php