query completions in code - threading issue?

[JASelep]

in Access 2007 I'm rendering a report
which kept having "can't open anymore databases" issues from the complicated
queries
if I manually run a series of 6 maketable queries the report runs fine from
the made tables and doesn't have issues

in trying to automate the process - the following in simplist terms is
attempted
'>>>
DoCmd.OpenQuery ("Table Block Mkr1")
DoCmd.OpenQuery ("Table Block Mkr2")
DoCmd.OpenQuery ("Summary Mkr")
DoCmd.OpenQuery ("Table Mkr3")
DoCmd.OpenQuery ("Client Table Mkr")
DoCmd.OpenQuery ("Crew Table Mkr")
'>>>
this is part of a "report header" event (on format)
the goal is to run to completion these 6 make table queries before getting
to the report data printing

it isn't completing as expected!!
it gets to about the third query and then states "A custom macro in this
report has failed to run, and is preventing the report from rendering."
it doesn't seem to matter which of the queries is the third one.

Is there a way to force single threading of these queries?
How to insure that the first query completes and table is available before
next query runs?
is there a better event trigger to use?
is there a series of event triggers that I might split current code into
smaller pieces to get sequentially run and completed?

setting the make tables queries into a macro executed from within the report
generates the "can't open anymore databases" error then "A custom macro in
this report has failed to run, and is preventing the report from rendering."

 

[Marshall Barton]

in Access 2007 I'm rendering a report
which kept having "can't open anymore databases" issues from the complicated
queries
if I manually run a series of 6 maketable queries the report runs fine from
the made tables and doesn't have issues

in trying to automate the process - the following in simplist terms is
attempted
'>>>
DoCmd.OpenQuery ("Table Block Mkr1")
DoCmd.OpenQuery ("Table Block Mkr2")
DoCmd.OpenQuery ("Summary Mkr")
DoCmd.OpenQuery ("Table Mkr3")
DoCmd.OpenQuery ("Client Table Mkr")
DoCmd.OpenQuery ("Crew Table Mkr")
'>>>
this is part of a "report header" event (on format)
the goal is to run to completion these 6 make table queries before getting
to the report data printing

it isn't completing as expected!!
it gets to about the third query and then states "A custom macro in this
report has failed to run, and is preventing the report from rendering."
it doesn't seem to matter which of the queries is the third one.

Is there a way to force single threading of these queries?
How to insure that the first query completes and table is available before
next query runs?
is there a better event trigger to use?

OpenQuery runs asynchronouslym si ut's really only good for
opening a select query in datasheet view.

Instead, use the Execute method:

Dim db As DAO.Database
Set db = CurrentDb()

db.Execute "Table Block Mkr1", dbFailOnError
db.Execute "Table Block Mkr2", dbFailOnError
. . .

If at all possible, this kind of thing should be done in the
report's Open event procedure. By the time you get to any
section's Format or Print events, the report has already
started using its record source table/query.