Post your favorite VB or VBA-based Access app automated compact-repair
Hello experts
{Token salutations, 'long time no see', where have you been, congrats on your multiple certs, you're still a big fat ex-PE meanie, where's the $10 you owe me, yada yada yada}
I have multiple Access '97 apps (yes, I know, '97) that needs to have a single batch job run about 30 times for 30 different clients, and for about 4-5 of these it gets real close to the 1Gb limitation and close to 5 hours long. Right now the runs are manual, but there is a table that contains each client that can be easily automated to support a loop to execute the batches sequentially.
The batcheswill also range anywhere from 5 minutes to 5 hours, so scheduling isn't really an option.
Upgrading to Access '2007 is an option, but that by itself isn't an answer I'm looking for.
Compact on Close is an option, but that by itself isn't an answer I'm looking for.
Token 'rewrite your code' comments aren't the answer I'm looking for, but I may award points based on style.
Questions:
(1) Does the newer versions of Access (2007, 2010) have the VBA-based ability to repair-compact?
(2) Does anyone have any VBx-VBA code that can repair-compact another Access db, specifically that does this within a loop, that can automatically detect when a batch job of unknown duration is complete before executing the next loop?
Thanks in advance.
Jim
{Token salutations, 'long time no see', where have you been, congrats on your multiple certs, you're still a big fat ex-PE meanie, where's the $10 you owe me, yada yada yada}
I have multiple Access '97 apps (yes, I know, '97) that needs to have a single batch job run about 30 times for 30 different clients, and for about 4-5 of these it gets real close to the 1Gb limitation and close to 5 hours long. Right now the runs are manual, but there is a table that contains each client that can be easily automated to support a loop to execute the batches sequentially.
The batcheswill also range anywhere from 5 minutes to 5 hours, so scheduling isn't really an option.
Upgrading to Access '2007 is an option, but that by itself isn't an answer I'm looking for.
Compact on Close is an option, but that by itself isn't an answer I'm looking for.
Token 'rewrite your code' comments aren't the answer I'm looking for, but I may award points based on style.
Questions:
(1) Does the newer versions of Access (2007, 2010) have the VBA-based ability to repair-compact?
(2) Does anyone have any VBx-VBA code that can repair-compact another Access db, specifically that does this within a loop, that can automatically detect when a batch job of unknown duration is complete before executing the next loop?
Thanks in advance.
Jim
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Shakier still, you could get out your hammer:
SendKeys "%(FMC)", [Wait]
(Set the WaitState argument accordingly)
SendKeys "%(FMC)", [Wait]
(Set the WaitState argument accordingly)
JH,
Nothing to add technically -- just nice to see you around.
Nothing to add technically -- just nice to see you around.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We decided to go a different route with this, and build a scheduler .mdb, with one open form and a Timer event, that will have table-driven batches.
We'll work in the repair-compact after each batch run to deal with the 1Gb size limitation.
There will be other benefits to the scheduler, mainly consolidating the menus (aka Switchboards) of all of them, centralizing Batches to make updates easier, being able to generate a list of all Access apps we have in production off of it (somewhere around 600), and tying that list to my logging .mde so we can completely report on report metrics such as when it ran, who ran it, batch or individual mode, did it blow up, what's the top 10 reports ran so we can optimize them, which reports were never run so we can consider killing them, and other stuff.
We looked at FMS total visual agent, and determined that it essentially ran DOS command lines, which we could do ourselves either in Access or in ActiveBatch.
Points awarded for generating ideas that led me to this solution.
Thanks all.
Jim
We'll work in the repair-compact after each batch run to deal with the 1Gb size limitation.
There will be other benefits to the scheduler, mainly consolidating the menus (aka Switchboards) of all of them, centralizing Batches to make updates easier, being able to generate a list of all Access apps we have in production off of it (somewhere around 600), and tying that list to my logging .mde so we can completely report on report metrics such as when it ran, who ran it, batch or individual mode, did it blow up, what's the top 10 reports ran so we can optimize them, which reports were never run so we can consider killing them, and other stuff.
We looked at FMS total visual agent, and determined that it essentially ran DOS command lines, which we could do ourselves either in Access or in ActiveBatch.
Points awarded for generating ideas that led me to this solution.
Thanks all.
Jim
dbengine.compactdatabase db1, db2