Kumba1
Technical User
- Aug 29, 2002
- 94
- 0
- 0
I have a delete query that is called by a VBA ADODB code passing a single parameter to the query for filtering... when I try to run the query through code, I get no errors, but the query does not execute right, however, when I double-click the query, and type in the parameter info, the query executes properly. Below is the ADP Code and the SQL Query used to execute the sequence... any help would be appreciated...
VBA Code:
Dim CMD As ADODB.Command
'Remove parts selected for Removal from the New Parts BOM
Set CMD = New ADODB.Command
With CMD
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.qryBOMSave"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Param1", adVarChar, adParamInput, 50, MachineName)
.Execute
End With
Set CMD = Nothing
Query Code:
ALTER PROCEDURE dbo.qryBOMSave
(@param1 varchar(50))
AS DELETE dbo.tblBOMParts
FROM dbo.tblBOMParts INNER JOIN dbo.tblTemp ON dbo.tblBOMParts.PrimaryKey = dbo.tblTemp.ForeignKey
WHERE (dbo.tblTemp.Remove = 1) and (dbo.tblTemp.Form = 2) and (dbo.tblTemp.Computer = @param1)
The function MachineName used in the VBA Code returns the string "Crash", which is the name of the local computer i'm running the code on. When I try to run this through VBA Code, the procedure runs, but does nothing, however, when I double click the query, and type in "Crash" for the parameter entry box, it executes properly. All the query does it take fields selected for delete from a temp table (tblTemp), and remove the fields from the live database table (tblBOMParts). The field tblTemp.ForeignKey is the Primary Key from tblBOMParts.PrimaryKey. I use it so I know which line i'm deleting from the live database. Thanks...
VBA Code:
Dim CMD As ADODB.Command
'Remove parts selected for Removal from the New Parts BOM
Set CMD = New ADODB.Command
With CMD
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.qryBOMSave"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Param1", adVarChar, adParamInput, 50, MachineName)
.Execute
End With
Set CMD = Nothing
Query Code:
ALTER PROCEDURE dbo.qryBOMSave
(@param1 varchar(50))
AS DELETE dbo.tblBOMParts
FROM dbo.tblBOMParts INNER JOIN dbo.tblTemp ON dbo.tblBOMParts.PrimaryKey = dbo.tblTemp.ForeignKey
WHERE (dbo.tblTemp.Remove = 1) and (dbo.tblTemp.Form = 2) and (dbo.tblTemp.Computer = @param1)
The function MachineName used in the VBA Code returns the string "Crash", which is the name of the local computer i'm running the code on. When I try to run this through VBA Code, the procedure runs, but does nothing, however, when I double click the query, and type in "Crash" for the parameter entry box, it executes properly. All the query does it take fields selected for delete from a temp table (tblTemp), and remove the fields from the live database table (tblBOMParts). The field tblTemp.ForeignKey is the Primary Key from tblBOMParts.PrimaryKey. I use it so I know which line i'm deleting from the live database. Thanks...
