If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query completions in code - threading issue?
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." -- Jim |
#2
|
|||
|
|||
query completions in code - threading issue?
JASelep wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|