How to run an action query based on 2 paramererized select-queries? 1

[surotkin]

Hello everyone,
I use the following code to run an action query in VBA:

Dim cmd_ADO As ADODB.Command
Dim prm_ADO As ADODB.Parameter

Set cmd_ADO = New ADODB.Command
Set cmd_ADO.ActiveConnection = CurrentProject.Connection
cmd_ADO.CommandType = adCmdStoredProc
cmd_ADO.CommandText = "MyActionQuery"

Set prm_ADO = cmd_ADO.CreateParameter("[prmDate]", adDate, adParamInput)
cmd_ADO.Parameters.Append prm_ADO
prm_ADO.Value = cdate("01/01/2006")

cmd_ADO.Execute Options:=adExecuteNoRecords

It works fine for simple action queries.

However, when I run a complex query based on 2 parameterized select-queries (all parameteres have the same name [prmDate]) the code doesn't work. It doesn't give me any error message either.
When I execute the same action query in Database Window (manually) it works fine.

Does anybody know how the parameters should be specified to run a complex action query based on a few parameterized select-queries?

Thanks.
surotkin

 

[PHV]

Define the foreign parameters in the action query too:
PARAMETERS [prmDate] DateTime;
UPDATE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surotkin]

Hi PHV,
thanks for your response.

The parameter [prmDate] is defined for each query (action and select-queries).

There is something else that prevents the action query from execution in VBA.

Any ideas?
surotkin

 

[surotkin]

Hi everybody,
I want to mention one detail.
I use LIKE in the parameterized select query among with the parameter [prmDate] in WHERE clause:

WHERE (((MyTable.Description) Like 'units*') AND ((MyTable.EffectiveDate)=[prmDate]))

I found that if I take away LIKE part from WHERE clause then it works. So, LIKE part is the reason that it doesn't work.

Question:
Should I declare the LIKE part in PARAMETERS clause amoung with

PARAMETERS [prmDate] DateTime;

?

Thanks
surotkin

 

[Larsson]

I think that you need to change Like 'units*' to Like 'units%'.
* is only for Access and when you use ADO you need tho use % instead.

 

[surotkin]

Hi Larsson,
you are absolutely right!!!
It works now!!!

Thanks a lot.
surotkin