Discussion Locked

MS Access Database Compacting

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

If anyone can provide advise on this topic it would be
much appreciated -
I work extensively with MSAccess 97 - one truly inefficient aspect
which I wish to get to the bottom of relates to the mb taken
up by active databases. The size keeps increasing requiring
continual compacting - is there maintenance I can perform
to overcome this feature ???

Many thanks

Eunan

8 replies

by seagull data services

Repairing and compacting is the maintenance you do to deal with this aspect of Access databases
Look up compacting databases for general info on the topic and CompactDatabase for an example ofhow this can be done in code.
Hope this helps
John

by donq

There is an Access feature that allows "Admin" to set Access up so it will "Compact" automatically whenever it is closed. ALL databases act the same way once this application property is set while NO databases are open. Repeat the process on any machine, or on the server + once on each front-end machine.

by crowmoor

There are also the posibility to add this code:

Dim fs as Object
Dim myfile

Set fs = CreateObject("Scripting.FileSystemObject")
dbEngine.CompactDatabase "Currentdb.mdb", "Nedb.mdb"

Set myfile = fs.Getfile("Currentdb.mdb")
myfile.delete
set myfile = fs.Getfile("Newdb.mdb)
myfile.rename "newdb.mdb", "Currentdb.mdb"

Not sure about the last line but as always you should debug code you get from places like this.

Hope this help!

Jesper

This discussion is closed to new replies.