Named Parameter Problem

[Deleted member 73419]

I have the following code which I cannot get to work using VBA:

Query "z_TestQuery"

SELECT PN, DE
FROM tblPN
WHERE (PN like [@Param1] AND MN like [@Param2]);

VBA code:

Function TestQuery2()

    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim cm As New ADODB.Command
    Dim PN As String
    Dim MN As String
    Dim RecordsAffected As Integer
  
    Set cn = CurrentProject.Connection
    cn.CursorLocation = adUseClient
        
    PN = "GE*"
    MN = "Al*"
    
    cm.ActiveConnection = cn
    cm.NamedParameters = True
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "z_testQuery"
    
    cm.Parameters.Append cm.CreateParameter("@Param2", adVarChar, adParamInput, Len(MN), MN)
    cm.Parameters.Append cm.CreateParameter("@Param1", adVarChar, adParamInput, Len(PN), PN)
    
    Set rs = cm.Execute(RecordsAffected)
    
    Debug.Print "Record count (RecordCount): " & rs.RecordCount
    Debug.Print "Record count (RecordsAffected): " & RecordsAffected
    Debug.Print

End Function

If I run the query directly and fill in the appropriate parameters when prompted, I get results returned so this query acts as you would expect.

But, if I run the VBA function, I do not get any results. At this point I am not concerned with the returned data itself, as I'll do something with the RecordSet in due course, but I am not able to populate the RecordSet.

I initially wondered whether I was not checking the RecordSet for the record count correctly, hence I tried two ways to get the record count in order to see if I was actually returning results.

Does anyone have any ideas as to why this doesn't work?

Thanks

 

[theDBguy]

Hmm, is this in the correct forum? If not, please let us know where to move it.

Is this line correct?

Set rs = cm.Execute(RecordsAffected)

I don't use ADO much, but I was expecting something more like:

Set rs = cm.Open(...)

 

[Galaxiom]

The wildcard character for ADO is the percent sign (%) .

 

[Deleted member 73419]

Hmm, is this in the correct forum? If not, please let us know where to move it.

Probably not as I hadn't noticed that I'd put it in the Visual Basic forum
I would have said that Modules & VBA forum was more appropriate, so please feel free to move as necessary...

Also tried

Set rs = cm.Open(...)

but this didn't work either.

The wildcard character for ADO is the percent sign (%)

Tried this and unfortunately it still doesn't work.

Thanks for your suggestions guys...

 

[theDBguy]

Thread moved to new forum. Sorry, I have no further suggestions at this time.

 

[CJ_London]

pretty sure you can't apply parameters when executing a query in vba, you need to hard code them into your sql str. Certainly applies to dao.

perhaps something like

querydefs("z_testQuery").sql=replace(replace(querydefs("z_testQuery").sql,"@Param2",MN),"@Param1",PN)

or alternatively if the query is a stored procedure in sql server, the parameters will have been declared in the stored procedure and are effectively populated using an optional variant array.

cm.Parameters(0).Value=MN
cm.Parameters(1).Value=PN

or perhaps

cm.Parameters("@Param2").Value=MN
cm.Parameters("@Param1").Value=PN

Creating a parameter dynamically does not make sense to me - the sql won't be using it.

 

[arnelgp]

It's a No, No.
from which DB does z_testQuery belong?
obviously Outside of the current db, otherwise you can just use a DAO method.
Your Connection is wrong, the connection should point to the external db.