use parameter with action query

[DataMiner]

Hi,
I have a complex action query that I need to run several times, varying a parameter in the where clause each time. The way I've always done this in the past is to just copy the whole sql string into my code, edit via code as necessary, and run it via db.execute. This works fine.

However, it seems like it would be easier to maintain if I could keep this query as a static querydef object, and just use VB to edit the parameter. It seems like I should be able to do this using the parameter object, but I'm very confused by it and it doesn't seem to work.

Here's what I'm trying to do:

currentdb.querydefs("SMT_ABC_refresh").parameters("VS")="HVLT"
currentdb.execute "SMT_ABC_Refresh",dbfailonerror

When I run this, I get an error message "too few parameters", so obviously my "hvlt" parameter is not getting picked up by the query.

Is my syntax just wrong? Or is this just not possible?

Thanks for any suggestions.

 

[KenHigg]

Could you simply have the query prompt you for the params using []'s?

 

[DataMiner]

No, sorry I didn't make this clearer. This query needs to run from code, unattended, as part of a whole bunch of other stuff. The code will be cycling through a table and varying the parameter according to what's in the table.


As I said, I DO know how to do this by copying the whole SQL string to code, but I was hoping that using the parameter object might offer a clearer and less messy way of maintaining this. It seems ridiculous to have to regenerate the whole big long messy SQL statement via code, when all I want to do is vary one tiny little part of it. It seems to me that's what the parameter object is for.... but I can't figure out how to use it.

 

[KenHigg]

currentdb.querydefs("SMT_ABC_refresh").parameters( "VS").value="HVLT"

???

 

[DataMiner]

Nope. Same result.

 

[KenHigg]

Hum... Seems like you could do it... Maybe someone else will jump in and help us - I'd like to see how to do this as well

 

[KenHigg]

If you can't get this to work, maybe you could do a global var and insert it into the query as a param

???