CODE EXAMPLE FOR SQL

sql stored procedure for access frontend

Sub spTest()
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long

IdValueToProcess = 2  ' test data

Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = True
qdf.Connect = "ODBC;DSN=myDb;Trusted_Connection=Yes;"
qdf.SQL = "EXEC dbo.getContact " & IdValueToProcess
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Debug.Print rst!LastName  ' just to make sure we got a result

rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
End Sub