S
Steve
I have a significant application in MS Access 2000. It compiles a bunch of
information to a temporary table, outputs it to Excel, copies the Excel file
to a user location, outputs a second report to Excel, and copies it to a
user location.
The program is combining, filtering, linking, calculating, etc data from
roughly 25 external linked tables and each table could have 500,000+
records. Since they are external, linked, and out of my control tables some
of them have keys and indexes that are appropriate for speeding up the
application and some are not. They are all Access 2000 based tables though.
The applications takes about 4 hrs to successfully compile the temp table
and output the first report to Excel. The first report is several hundred
excel pages and 20,000 lines with 100 columns.
However, I have a problem in that no other command after outputting report 1
will work. Not even a "docmd.quit" - for example:
after report 1 is sent to Excel if I try to copy the file it tells me the
file is still in use
after report 1 is sent to Excel if I try to output report 2 as the next step
it tells me the outputto command isn't available at this time
I have tried inserting up to a 30 minute pause between outputting report 1
and any next command, but no luck.
report 2 is smaller, but even if I try outputting report 2 then 1, I get the
same command isn't available
same deal, if I try to docmd.quit after the report
So it looks to me like everything is fine through sending the report to
Excel but for some reason Access isn't releasing the file or program for
further processing. However, by inserting breaks, if I have the 30 minute
pause in there it does process the pause but then fails on the next "action"
command. Report 1 is fine, finished, usable, and complete so I don't have a
clue why it can't go on to another action - especially since it executes the
pause.
This is running on a dual 550Mhz Xeon processor server with 512M of RAM and
at least 25G of disk space. The database and file sizes are all reasonable.
ie, the db might be 25M, 50M, 100M, but nothing greatly beyond. The Excel
file might be 2M - 5M so not outrageous either.
Can anyone offer any info or ideas on how I can get it to keep processing?
information to a temporary table, outputs it to Excel, copies the Excel file
to a user location, outputs a second report to Excel, and copies it to a
user location.
The program is combining, filtering, linking, calculating, etc data from
roughly 25 external linked tables and each table could have 500,000+
records. Since they are external, linked, and out of my control tables some
of them have keys and indexes that are appropriate for speeding up the
application and some are not. They are all Access 2000 based tables though.
The applications takes about 4 hrs to successfully compile the temp table
and output the first report to Excel. The first report is several hundred
excel pages and 20,000 lines with 100 columns.
However, I have a problem in that no other command after outputting report 1
will work. Not even a "docmd.quit" - for example:
after report 1 is sent to Excel if I try to copy the file it tells me the
file is still in use
after report 1 is sent to Excel if I try to output report 2 as the next step
it tells me the outputto command isn't available at this time
I have tried inserting up to a 30 minute pause between outputting report 1
and any next command, but no luck.
report 2 is smaller, but even if I try outputting report 2 then 1, I get the
same command isn't available
same deal, if I try to docmd.quit after the report
So it looks to me like everything is fine through sending the report to
Excel but for some reason Access isn't releasing the file or program for
further processing. However, by inserting breaks, if I have the 30 minute
pause in there it does process the pause but then fails on the next "action"
command. Report 1 is fine, finished, usable, and complete so I don't have a
clue why it can't go on to another action - especially since it executes the
pause.
This is running on a dual 550Mhz Xeon processor server with 512M of RAM and
at least 25G of disk space. The database and file sizes are all reasonable.
ie, the db might be 25M, 50M, 100M, but nothing greatly beyond. The Excel
file might be 2M - 5M so not outrageous either.
Can anyone offer any info or ideas on how I can get it to keep processing?