Use of SQL Server 7 Stored Procedure in Access Form

[heorotlinea]

Hello,

I have a database in SQL Server 7, and I am using a Microsoft Access Project front-end for forms. I have coded a stored procedure with parameters on the SQL Server side, and it runs as expected when I execute it. What VB code do I use to call this stored procedure from a "button-click" function on the Access form? I need the stored procedure to run whenever the button is clicked, and the references that I have used have not given me the answer.

For reference, the name and parameters of the stored procedure are as follows:
CREATE PROCEDURE spDuplicateWeeklyStatus
@currUserID VARCHAR(7),
@currDate SMALLDATETIME,
@newDate SMALLDATETIME

I also have the following variables set up in my "button-click" function so I can pass the data to the stored procedure, and I have code set up to put the desired data into these variables:
Dim currUserID As String
Dim currDate As Date
Dim newDate As Date

I would very much appreciate any help you can provide. Thank you!

[andi_g69]

You can do it like this. Check Access help for the properties of the QueryDef object.

Dim myq As QueryDef
Dim QName

QName = "spDuplicateWeeklyStatus @currUserID = '" & currUserID & "', @currDate = '" & currDate & "', @newDate = '" & NewDate & "'"

Set myq = CurrentDb.CreateQueryDef("")
myq.Connect = <here goes your connection string>
myq.ODBCTimeout = 90
myq.ReturnsRecords = False
myq.SQL = QName
myq.Execute
myq.Close