Excel passing parameters to Access for queries

[RichardMGreen]

Hi all

I've got a piece of code in Excel which links to an Access database and runs queries in it.
Some of the queries need parameters which are being passed to it using this piece of code:-

If param1 <> "" Then cmd.Parameters(0) = param1

The query I'm running requires 1 parameter which is contained in param1 (and I've stepped through the code to make sure it's there).

When I try to pass the parameter, I get the following error message and the code stops:-
Item cannot be found in the collection corresponding to the requested name or ordinal

If I miss out the the line of code above (leaving the parameter empty) and try to execute the query, I get the following error message:-
Too few parameters. Expected 1

It looks like I need to pass the parameter, but when I do it isn't accepted.

Anyone any ideas?

 

[RichardMGreen]

I'll give that a go and see what happens.

I've also built a second query in the same manner and that accepts the parameter without a problem.

Do you want the SQL from that to see if I've done soemthing dim?

 

[RoryA]

Not if it works.

 

[RichardMGreen]

Just tried that but I'm still getting the same error.

 

[RoryA]

What if you add:

cmd.parameters.refresh

before you try and assign them? (I am assuming this code does work for other parameter queries?)

 

[xenou]

If param1 <> "" Then cmd.Parameters(0) = param1

If your query takes a parameter then it must be provided (you don't have the option of not setting it). So better:

If param1 <> "" Then 
    cmd.Parameters(0) = param1
Else
    MsgBox "No member ID provided.  Query aborted.
    '//abort the query ...
End If

BTW, I found this a useful article on ado parameters in general:
http://www.vb6.us/tutorials/using-ado-and-stored-procedures-vb6

You can, by the way, avoid parameters altogether and just pass the sql string (then you only need to substitute the member code in the SQL string) ... e.g.

member_code = Range(X).value
sql_string = "SELECT Blah FROM Blah WHERE MemberCode = " & member_code & ";"

I do this quite a lot as a way of "parametizing" without actual parameters ... I think from laziness not bothering with writing code for parameters, which I don't do very often.

 

[RichardMGreen]

What if you add:

cmd.parameters.refresh

before you try and assign them? (I am assuming this code does work for other parameter queries?)

I'm still getting the same error about the item not being found.

Bizarrely, if I do a count of the parameters for this particular query I get 0 (zero), but if I try to run the query without passing parameters it tells me it expects 1.

 

[james_lankford]

I'm confused
maybe I missed it somewhere, but in the excel vba code you posted I see this

        strSQL = "SELECT * FROM " & strQry
        cmd.CommandType = adCmdText
        cmd.CommandText = strSQL
        cmd.ActiveConnection = conn
'----- Pass parameters if needed/available -----
        If param1 <> "" Then cmd.Parameters(0) = param1

where does that simple select statement use a parameter ?

I see that you assign a parameter to the command object, but I don't see where the sql actually uses or even needs the parameter

anyway, if use a sql statement (that already exists in MS Access) that uses parameters then I think

cmd.CommandType = should be adCmdStoredProc

cmd.CommandText = should be the NAME of the access query

but if you're just going to build your sql in VBA (as you're doing with -- strSQL = "SELECT * FROM " & strQry -- ) then why do you need parameters at all ?
You can just build the sql and put the "parameter" in it

strSQL = "SELECT * FROM " & strQry & " where some_column = " & param1

 

[RichardMGreen]

The query in Access requires 1 parameter (as do some other queries).
The code I originally posted works fine for the others, just this one that I'm having trouble with.

To make matters slightly more difficult, not all queries require a parameter.

@James - I need the code to loop through a lot of queries and I've used this method before. It's just not working for this one query.

 

[dk]

When the code falls over what is the value of strQry? (I am assuming that it is the name of your query that you posted the SQL for but want to clarify).

DK

 

[RichardMGreen]

DK - Yeah, it is. Don't know if it makes any difference but the query name is qry_Front_Page_Data