Running a parameterized query in code

Topic

New Reply

I think I solved it — had to change datatypes in the target table, seems to be working now.

This was working yesterday! but not today. i have an append query and using

[codebox] Dim cmd As New ADODB.Command
Dim rst2 As New ADODB.Recordset

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = “append_query”
Set rst2 = cmd.Execute(, SelectedRow)[/codebox]

It occasionally works but most of the time it errors out with

Expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.

the append query in question is an append query to another table in the db, and it expects a parameter:

INSERT INTO a whole bunch of values WHERE ((([].FORM_DLN)=[Enter DLN]));

The append query was re-edited to map to some different fields in the source data but otherwise not changed. if i run it manually it is fine.

Is there a way to attach a parameter to RunSQL?

TIA

Reply | Quote

Replies

Parameters To Queries in VBA can be a pain.

If Access can see them on a form using normal syntax they shoulld work with OpenQuery Method of DoCmd

but the Parameters would need to be pointed to a form.

An alternative might be to use DAO query Defs.

Cannot give you exact solution but if you can provide them all at execute time, then something like this
ought to work.

Dim qdf As DAO.QueryDef

Set qdf = CurrentDB.OpenQueryDef(“Name Of Query Here”)

‘Now we’ll assign values to the query using the parameters option:

qdf.Parameters(0) = Provide Parameter 0 here
qdf.Parameters(1) = Provide Parameter 1 here etc (Allow for All Parameters)

qdf.Execute

Set qdf=Nothing

Worth a go anyway.

Reply | Quote

Parameters To Queries in VBA can be a pain.

If Access can see them on a form using normal syntax they shoulld work with OpenQuery Method of DoCmd

but the Parameters would need to be pointed to a form.

An alternative might be to use DAO query Defs.

Cannot give you exact solution but if you can provide them all at execute time, then something like this
ought to work.

Dim qdf As DAO.QueryDef

Set qdf = CurrentDB.OpenQueryDef(“Name Of Query Here”)

‘Now we’ll assign values to the query using the parameters option:

qdf.Parameters(0) = Provide Parameter 0 here
qdf.Parameters(1) = Provide Parameter 1 here etc (Allow for All Parameters)

qdf.Execute

Set qdf=Nothing

Worth a go anyway.

I’ve worked with querydef in running Stored Procs in SQL Server from Access VBA. The method I outlined above seems to be much the same, but uses ADO instead of DAO. Can’t remember which is better!! Don’t care right now! Anyhow, I have the thing working. Thanks for the input, and FWIW specifying params using querydef might be a little easier to work with. i assume to order of params is somehow set in the query, say reading left to right….unless it’s an arabic query of course.

Reply | Quote

i assume to order of params is somehow set in the query, say reading left to right….unless it’s an arabic query of course.

To work with parameters in code, it’s best to declare them explicitly.
You can do this in the design view of a query, by selecting Query | Parameters…, or by specifying a PARAMETERS clause in the SQL string of the query, e.g.

[sql]PARAMETERS [Which Country] Text (255), [Which Date] DateTime;
SELECT …[/sql]
When you want to set the parameters using code, Parameters(0) corresponds to [Which Country] etc.

Reply | Quote