sql stored procedure for access frontend

1CREATE PROCEDURE getContact 
2    -- Add the parameters for the stored procedure here
3    @id int = 1
4AS
5BEGIN
6    -- SET NOCOUNT ON added to prevent extra result sets from
7    -- interfering with SELECT statements.
8    SET NOCOUNT ON;
9
10    -- Insert statements for procedure here
11    SELECT * FROM myContacts WHERE ID=@id
12END

1Sub spTest()
2Dim qdf As DAO.QueryDef, rst As DAO.Recordset
3Dim IdValueToProcess As Long
4
5IdValueToProcess = 2  ' test data
6
7Set qdf = CurrentDb.CreateQueryDef("")
8qdf.ReturnsRecords = True
9qdf.Connect = "ODBC;DSN=myDb;Trusted_Connection=Yes;"
10qdf.SQL = "EXEC dbo.getContact " & IdValueToProcess
11Set rst = qdf.OpenRecordset(dbOpenSnapshot)
12
13Debug.Print rst!LastName  ' just to make sure we got a result
14
15rst.Close
16Set rst = Nothing
17qdf.Close
18Set qdf = Nothing
19End Sub