Too few parameters. Maketable query works, but db.execute does not

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a maketable/append query (I've tried it both ways) that uses two
parameter references to combo boxes on an open form to build/append to a
table that is the record source for a subform.

When I run this query (either maketable or append) from the query window, it
works just fine. But when I run it from code (currentdb.execute "queryName")
it generates the "too few parameters. Expected 2" error.

Any ideas why the query runs but generates the error when run from code?
Any suggestions?

I'm doing it this way because there are some aggregations involved and the
query taps into a table containing over 1.7 million records. By creating
this new table, I can then sort and filter the subform without having to
rerun the underlying query, which generally takes several minutes.
 
Dale said:
I have a maketable/append query (I've tried it both ways) that uses two
parameter references to combo boxes on an open form to build/append to a
table that is the record source for a subform.

When I run this query (either maketable or append) from the query window, it
works just fine. But when I run it from code (currentdb.execute "queryName")
it generates the "too few parameters. Expected 2" error.

Any ideas why the query runs but generates the error when run from code?
Any suggestions?

I'm doing it this way because there are some aggregations involved and the
query taps into a table containing over 1.7 million records. By creating
this new table, I can then sort and filter the subform without having to
rerun the underlying query, which generally takes several minutes.
Click to expand...


When you run the query from the query design window, you're
really asking Access to run it for you. Access is very
helpful and figures out what the parameters are and how to
take care of applying their values.

When you Execute the query in the VBA environment, you have
to these things in your code:

Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs!yourquery
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Parameters(1).Value = Eval(qdf.Parameters(1).Name)
qdf.Execute dbFailOnError
. . .
Set qdf = Nothing
Set db = Nothing
 
Marshall,

Thanks for this explaination. Now that you have explained that part, I need
to elaborate.

The maketable/append query uses two other queries, which do some
intermediate aggregations, as its data sources. It is one of these
"sub-queries" that has the parameters, so how do you think I should go about
this given this extra little bit of information?

Thanks

Dale.
 
Dale said:
Thanks for this explaination. Now that you have explained that part, I need
to elaborate.

The maketable/append query uses two other queries, which do some
intermediate aggregations, as its data sources. It is one of these
"sub-queries" that has the parameters, so how do you think I should go about
this given this extra little bit of information?
Click to expand...

Three approaches come to mind. One is to construct the
query's SQL in code, probably not worth it in this case.

Another is to ask Access to run the query by using RunSQL
instead of Execute.

My preferred approach is to find a way to do the job without
a temporary table. MakeTable queryies cause bloat, increase
the chance of corruption and are only rarely worth the
effort. If you really do have a good reason for using a
temporary table, see Tony's site for tips on how to park the
temp tables in a temp mdb file:
http://www.granite.ab.ca/access/bloatfe.htm
--
Marsh
MVP [MS Access]


 
I've used the method of parking them in another mdb before, and that works
pretty. Thanks for the reminder.

What I was getting at is whether your instructions for opening the querydef,
passing the parameters, etc will still work, since the parameters are
actually in a separate query from the one which is being run at the time.
I'll give your previous suggestion a look when I get to the office on
Monday.

Thanks for the help.
Dale

Marshall Barton said:
Dale said:
Thanks for this explaination. Now that you have explained that part, I need
to elaborate.

The maketable/append query uses two other queries, which do some
intermediate aggregations, as its data sources. It is one of these
"sub-queries" that has the parameters, so how do you think I should go about
this given this extra little bit of information?
Click to expand...

Three approaches come to mind. One is to construct the
query's SQL in code, probably not worth it in this case.

Another is to ask Access to run the query by using RunSQL
instead of Execute.

My preferred approach is to find a way to do the job without
a temporary table. MakeTable queryies cause bloat, increase
the chance of corruption and are only rarely worth the
effort. If you really do have a good reason for using a
temporary table, see Tony's site for tips on how to park the
temp tables in a temp mdb file:
http://www.granite.ab.ca/access/bloatfe.htm
--
Marsh
MVP [MS Access]


window,
it
Click to expand...
Click to expand...
 
Sorry, Dale, I got carried away there and skipped typing the
key part of my response.

No. Each query has it's own set of parameters, so you would
not be able to set the parameters for one query when
executing a different one. When I think about this
situation, I come up blank on how to supply parameters to a
query of queries unless you execute each one as a separate
Select Into -- yuch.
 
Back
Top