pokeymonsc
Technical User
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 [base_data.mmes_number]
What I'm unsure about this time if this 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 want to make absolutely sure that the 'mmes_number' that's in the current record of the 'base' form is passed to the SP, otherwise I'm worried that ALL the records in the database will be deleted not just those with this mmes_number.
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.
EXECUTE spDeleteChildRecords [base_data.mmes_number]
What I'm unsure about this time if this 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 want to make absolutely sure that the 'mmes_number' that's in the current record of the 'base' form is passed to the SP, otherwise I'm worried that ALL the records in the database will be deleted not just those with this mmes_number.
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.