I am running a few modules of VBA code. In the middle of running the code crashes as Access reaches its max size of 2GB; but, if I compress the database at that point it is only 200MB.
Is it possible to compress the database at regular intervals while the code is running?
You can only compact a database that isn't open (or, compact it to a differently-named file).
See here for steps.
Yes, you can. However you have to close all recordset and database variables, forms and reports that are using the database file. You can test this yourself by running your code and seeing if the LDB file no longer exists. Also all users other than yourself have to be out of the database file of course.
You can loop through the Forms collection (which is actually the open forms) and the Reports collection to clsoe them all. Of course, once you're finished processing, you will need to reopen any autostart forms, etc, etc.
how do your table indexes look? I'm not sure what type of application it is, but 2gb from a 200mb compacted database doesnt sound right. Are your tables linked from the backend database file, or how is the data setup? Splitting the table data from the query/forms/reports should help.
I'm not sure about vb, but I always did it from a command line by calling the "/compact" commandline switch. I think you can call the db via commandline switches just the same as you open any file in vb easily.
Per MS
Compacts and repairs the Access database, or compacts the Access project, specified before the /compact option and then closes Access. If you omit a target file name following the /compact option, the file is compacted to the original name and folder. To compact to a different name, specify a target file. If you don't include a path in target database or targe Access project, the target file is created in your My Documents folder by default. In an Access project, this option compacts the Access project (.adp) file but not the SQL Server database.