Pass Through in Query Designer 1

[pokeymonsc]

I've created a pass-through query to a stored procedure on SQL-Server using Query Designer in access. I've done this with another SP and the method works fine. The code for this SP is:
EXECUTE spDeleteChildRecords [mmes_number]

What I'm unsure about this time is the correct code to pass a parameter (mmes_number) to the SP. I have a button on my Base form:
Dim stDocName As String
stDocName = "DeleteChildRecords"
DoCmd.OpenQuery stDocName, acNormal, acEdit

and this will run the SP but I haven't worked out how to pass the parameter to the query and on to the SP. What I want to do is pass the 'mmes_number' that is in a textbox on the form where my button as a parameter to the query and on to the SP. The 'OpenQuery' method in the button code doesn't allow for this and playing with the EXECUTE code (EXECUTE spDeleteChildRecords (Basedata.mmes_number))(Form.txtbox) didn't work either. Any suggestions?

Some of the code in the SP are as follows:

CREATE PROCEDURE dbo.spDeleteChildRecords (@id1 varchar(10))
AS
BEGIN
IF @id1 IS NOT NULL
DELETE FROM BASE_DATA WHERE MMES_NUMBER = @id1
(Repeated with 21 other tables)
END
GO

There are a lot of tips in the forum on how to use VBA code to access the server directly and run the SP, but nothing on using the query designer to set up the pass-through and how to send a parameter to the query.

 

[PHV]

I think you have to play with the ADODB.Command object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kpeasley]

You can change the query dynamically by using query def before opening it.
I'm assuming that Basedata.mmes_number is your textbox that holds the value you want to pass to the stored procedure.

     Dim db As DAO.Database
     Dim qry As DAO.QueryDef
     Set db = CurrentDb
     Set qry = db.QueryDefs(stDocName)
        With qry
        .sql = "exec spDeleteChildRecords ('" & Basedata.mmes_number & "')"
        End With
        DoCmd.OpenQuery stDocName