Compact and Repair

[Guest]

I was hoping someone could elaborate on how to automate compact and repair on
a db. I have a db that i wish to implement for a charity. It is split
(front-end ... Back-end...). the db is half way around the world and on a
network that I cannot access so I need it to operate independantly.
Typically, I always administered my Dbs and ensured they were optimal
(compact and repair). In this case, I need the db to perform such
maintenance on it's own. can someone point me in the right direction or give
me some guidance from experience on what works and does not. Or even a
sample db to learn from.

Thank you, as always, for your insight,

Daniel P

 

[Guest]

Hi Daniel,

Check out the function:

DBEngine.CompactDatabase sSourceName, sDestinName, , , ";PWD=TEST"

Hope this helps

 

[Guest]

Hi Daniel

You can use the VBA CompactRepair method but note that:

1. You cannot Compact/Repair a database from within itself. Thus, you could
Compact and Repair your Back-end database from the Front-end which is
probably what you want to do.
2. When carry out the procedure you create a new database which has to be
named differently from the original.

So, I would:
- CompactRepair the b/e to a new database with a temporary name.
- Delete (or better archive) the original b/e
- Rename the temp b/e as the original.

Something along the lines of:

Application.CompactRepair "C:\path to be database\be.mdb",
"c:\path..\temp.mdb"
Kill "c:\path...\be.mdb"
Name "c:\path...\temp.mdb" As "c:\path...\be.mdb"

The Kill statement is rather drastic and should only be used if you know
that a suitable back-up of the original has been taken. If not, then better
to re-name the original as an archive. e.g.

Instead of the Kill ... line use,
Name "c:\path...\be.mdb" As "c:\path...\archive\be - " & Now() & ".mdb"

You will, of course, end up with an increasing number of archive copies so
will probably need to write a routine to clear out the older ones as
appropriate - this will be straight forward as you have used the Now()
function so can easily sort the files into chronological order.

Good luck.

BW

 

[balu]

if the same database protected by password how to use compact & repair please
with thanks