Database maintenance (1 Viewer)

[Bee]

Hi folks,

I have searched on this, but did not find any article that is comprehensive enough. I wonder if anyone can advise on database maintenance routines particularly for Microsoft Access.

I know:

1. Regular Back ups
2. Compact and repair database

Please fell free to add on to this list.

Thanks,
B

 

[statsman]

COMPACT AND REPAIR

Click on Tools - select Database Utilities - select Compact & Repair Database

Or to do it automatically:
With the database open go to
Tools - Options - General
and click on the "Compact on close" box.
The database will repair and compact every time you close it.
I don't know why they make this an option, it should be standard.

 

[Moniker]

It's an option because if it's the end of the day and you're trying to leave the office and your DB in 800MB, it takes about 15 minutes to compact/repair it. (I have DBs this size and larger.) For most DBs, say 50MB and under, it's fine. Otherwise, it's just an unwanted pause between you closing the DB and Access "giving you permission" to do so.

 

[statsman]

Point taken. I've never had one that big.

 

[Libre]

I regularly compile the db.
Everytime I write some new code, with the module window open, I click Compile, under the Debug menu.
This DOES NOT create a compiled .mde file.
It runs through ALL the code in ALL modules and locates ANY compile errors.
For example, if you have any open IF statements without corresponding END IF statements;
Any undeclared variables or rendundantly declared variables;
Any Select without End Select;
And With without End With;
etc.
I beleive it MIGHT increase the size of the db slightly, but it will run faster, and of course you are also eliminating any compiler errors which will crash the program if the code is executed at runtime.