If the procedures can complete sucessfully when run independently, then it should be possible to process them without running into memory problems. In a single threaded environment like Access VBA code runs in, you could conceivably use too many large objects without releasing them before using additional large objects. If it is a memory problem, you can close and set to Nothing any objects that are no longer needed.
Macros do not have error handling/recovery capability. However, in VBA, you can trap and handle or log errors. Your post states that you use VBA but I would suggest that you make sure that there is only a single RunCode line in your macro to call a single Function that calls separate procedures for each query and export. You may reuse the procedure that runs the appends by passing in an SQL string or Query Name and you can reuse the export procedure with appropriate parameters as to source and destination. Splitting the procedure up this way helps ensure that you create and clean up objects sequentially as needed. Make sure you have an error handler in each procedure. Logging the error number and message string to a table will help a great deal if it happens when you are not present.
Instead of relying on a Macro, you may set an opening form in the application startup (Tools menu - Startup...). In the open event, have the form check the system time (Now()) and if it is within 2 minutes of your scheduled time, use it to call your procedures. Assuming you have proper error handlers in place, you have a chance of getting a more useful error message.
Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]
Scheduled Task
