Using a macro to print reports from multiple databases

[Guest]

I have two seperate complex databases for completing month-end reports. Each
database has macros set up to print all the reports for that specific office.
I am trying to collate the printing jobs. For example: I want macro 1 from
database A to run, then macro 1 from database B, then macro 2 from database
A, then macro 2 from database B, and so on. Printing them and collating by
hand is a huge waste of time. Any assistance would be appreciated. Thanks! JMC

 

[Steve Schapel]

JMC,

What is the reason these are in two separate databases? This appears to
be a basic design flaw. I can't think of a way of doing what you ask,
unless you integrate all the macros and reports into a single
application file. This would probably be quite easy to achieve.

 

[Guest]

Steve, These databases have been separate for years. They have completely
different data sources plus one is monthly and the other is quarterly. April
is an end of a quarter and Management for the first time has requested that
all the reports be shown together. If I were to combine them, the database
would approach 2-3MB which I have learned from past experiences to be an
"unsafe" size.

I did program a macro with RunApp to open the database followed by a run
macro command. This worked, but now I have a new problem. The reports from
the second database don't always make it to the printer queue before the next
set of reports from the first database. Is there a way to delay or pause a
macro until the previous action is completed before proceeding to the next?
Thanks in advance.

JMC

 

[Steve Schapel]

JMC,

I suppose it would be possible to devise some sort of routine based on
reports being run from macros on the Timer event of forms, and set the
Timer Interval of the forms to an amount greater than the length of time
that the previous report takes to print. This would taks a bit of time
to put together, and would be quite messy, but you could probably make
it work.

If it was me, and if this was to be a regular requirement, I would make
a separate application file, with all the reports and their underlying
queries, and link from this to the tables in the two existing databases.
Then run the reports from this integrated application.

I asume you already have each of the exisiting databases split into a
front end application file, and a back end data file? Well, it's just
the same concept, except link from one front end to both backends.

I am not sure about the meaning of your comment re 2-3 MB being an
unsafe size. I assume this is a typo, since you previously described
these as complex databases. The limit for an MDB file is 2 GB.
Anything which could be described as a complex database, I imagine,
would likely have a frontend of at least 10 MB, and backend database
files of hundreds of MBs are common. What's "unsafe"?

 

[Guest]

Thanks, Steve. It was a typo. It would be over 2 GB. I will go with the
integrated application as my solution.

 

[Steve Schapel]

Best wishes with the project, JMC.