Advice on Database Design

[JoeM]

I have a large complex project that I am trying to figure out how to best program.

Basically, I have a large Access database that creates detailed reports for a number of clients. There is also an Excel VBA piece that formats each report. I was attempting to control the whole thing from Access, but that may not be the best option.


Here are the key details:
- Within the Access database, there is a listing of clients to run this for.
For each client, the process is as follows:
- Access first copies over all DBF files that it needs to link to create the reports.
- Access runs a bunch of queries (some SELECT and some ACTION to create all the report data).
- Excel Macro pulls all the needed information out of Access and formats into nice looking Excel reports.
- It then repeats for the next client.

If I run it for one client, by running the Access database piece, closing Access, and then opening and running the Excel macros, it works fine. However, we are trying to automate the whole process. I was initially trying to control the whole process from Access, but maybe using a batch file to call Access and Excel is the way to go. Here are the issues I am encountering when trying to control everything form Access:
- When I call to run the Excel macro from inside Access, Excel gets mad and won't access the Access data, saying Access is already open and locked. I am trying to extract the Access data using Excel in the following manner (this is where it gets angry):

    Set daoDatabase = DBEngine.OpenDatabase(strDB)
    Set daoQueryDef = daoDatabase.QueryDefs(strQueryName)
    Set daoRecordSet = daoQueryDef.OpenRecordset

- Some of this data is quite extensive, and the database could bloat while number for a number of clients (since Append/Update queries are being run).

If I could resolve these two issues without a ton of effort, I would like to use that methodology. If not, here is my back-up idea:
- Use a batch file to control the process
- Open Access, run the data for the first client, mark them as complete in Access, then close Access
- Compact Access database (either with Compact on Close or some other methodology)
- Open and run Excel macro, then close
- Re-open Access, run the next client not marked as complete, and repeat the process above

Thoughts?

[JoeM]

It turns out that the issues I was getting accessing the Access data from Excel were due to a bad file path, not any issues with the code. So please disregard that.

So I think I should be OK other than the whole "Compact and Repair" issue. I remember in old versions of Access, you could not programmatically tell an Access database to Compact itself. Is that still true in the newer versions?