Running a Delete Parameter Query from VBA

[Kumba1]

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...

I'm running Access 2002 SP-2 with MS SQL Server 2000...

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...

 

[cjowsey]

Have you tried setting a breakpoint in the VBA code (press F9 on the line you want to break)and stepping through through code with F8. You can then check the contents of "MachineName" before the .Execute line.

Secondly, if MachineName is a function, shouldn't it have parentheses at the end of it like this MachineName()?

 

[Kumba1]

The machine name is passing correctly out of the function, but the query is still not executing properly...

 

[Kumba1]

Does anyone have any other approaches to accomplish what i'm trying to do? Maybe there's a better/simpler way that i'm just not seeing. Thanks...

 

[cjowsey]

Have you tried using a string variable to hold machinename and using that in the CreateParameter call? For example:

Dim CMD As ADODB.Command
[red]Dim strMachineName as String

strMachineName = MachineName()[/red]

'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, [red]strMachineName[/red])
    .Execute
End With
Set CMD = Nothing

or alternatively, have you tried splitting .Append and .CreateParameter into separate lines something like this?

Dim CMD As ADODB.Command
[red]Dim param As ADODB.Parameter[/red]

'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
    [red]Set param = .CreateParameter("Param1", adVarChar, adParamInput, 50, MachineName())
    .Parameters.Append param[/red]
    .Execute
End With
Set CMD = Nothing