Parameters in an Access query?

Question

I am programming an application in VBA that uses ODBC and querying for large data sets. I have found that putting the SQL in a string - or concatenated strings - is messy and doesn't seem to work for anything over the standard string length.

I have taken the route of using the query builder and saving the query with parameters. I am attempting to access this query programatically. However, this query has 3 parameters and I am not having luck with articles and answers I have found.

Here is the code:

Dim qdf
Set qdf = CurrentDb.QueryDefs("Kilometers")
qdf.Parameters("startDate").value = startDate
qdf.Parameters("endDate").value = endDate
qdf.Parameters("workshopCode").value = garageRst!ID

Set rs = kpidb.OpenRecordset("Kilometers")

I have the parameters in the sql statement called "Kilometers" like so:

PARAMETERS startDate DateTime, endDate DateTime, workshopCode Text ( 255 );

And in the statement use square brackets to denote the parameters. I also have them set up in the access tool for parameters.

When I run my code, Access is saying that it needs 3 parameters (error 3061)

Is this the way I should be achieving my end goal? What am I doing wrong?

Have you tried prepending an @ to each parameter name?

mrtig
– mrtig

2014-08-14 16:35:00 +00:00
Commented Aug 14, 2014 at 16:35 — mrtig
– mrtig, Commented Aug 14, 2014 at 16:35

Marty · Accepted Answer · 2014-08-14 16:49:49Z

6

You need to open your recordset using the QueryDef object you created. Your corrected code should look like:

Dim qdf
Set qdf = CurrentDb.QueryDefs("Kilometers")
qdf.Parameters("startDate").value = startDate
qdf.Parameters("endDate").value = endDate
qdf.Parameters("workshopCode").value = garageRst!ID

Set rs = qdf.OpenRecordset

Hope it helps. Good luck with your project.

answered Aug 14, 2014 at 16:49

Marty

2,0461 gold badge13 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parameters in an Access query?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related