Compacting MDB File.

Question

Is there any way to compact the shared mdb file. I mean this file is is always in use. I am looking for the way to compact mdb file without disconnecting users (while the mdb file is in use).... Access version is 2003.

Answer 1

No, to compact the database you must open it in exclusive mode.

Answer 2

Is your MDB in use 24 hours a day? If so I would suggest that using Access/Jet to store the data is not a good idea as you need to do backup. Alternatives include MS SQL Server which is available in a free edition with size limitiations of 4 Gb.

Now if your backend (BE) MDB is only busy 12 or 20 hours a day then when the user exits the FE attempt to rename the backend MDB preferably with todays date in the name in yyyy-mm-dd format. Ensure you close all bound forms, including hidden forms, and reports before doing this. If you get an error message, oops, its busy so don't bother. If it is successful then compact it back.

See my Backup, do you trust the users or sysadmins? tips page for more info. http://www.granite.ab.ca/access/backup.htm