compact/repair backend db (1 Viewer)

[Hammy]

In the esteemed opinion of those here, what is the best way to handle the following senario (I have done some research and can't seem to find a good solution, so now I am here where I have recieved A+ help!)....

I have a db split into a front end file with forms,queries and reports and a back end file with the linked tables (for network speed). I am not overly concerned about running regular compact/repairs on the front end, however I want to implement a solution to regularly compact and repair the backend tables.

I would like to put in some solution that allows the user to simply click a button on the switchboard that will perform the compact on the linked tables file, at their discression. However, I can't determine how to set this up....and know it is working and will work in a multi user environment.

The only other option I can see is to enable compact on close for the backend file. However, I am not certain how this would react in a multi user network environment.

Any suggestions?
Hammy

 

[Autoeng]

According to the Access help file Compact on close will not function if more than one user is in the db. I have had it turned on for several months now and have not experienced any problems. See below from the help file.

Compact an Access database or Access project automatically when you close it
Microsoft Access can automatically compact a Microsoft Access database or Microsoft Access project every time you close it.

Open the Access database or Access project that you want Microsoft Access to compact automatically.


On the Tools menu, click Options.


Click the General tab.


Select the Compact On Close check box.
Note Compacting does not occur if you close a multiuser (shared) database while another user has it open.

Autoeng

 

[Hammy]

Yeah, but this db is going to be quite large over time. COmpacting on close will take a lot of time for every close will it not?

 

[Autoeng]

Possibly, I don't know. The only time I can imagine that this would do anything bad is the last person in shuts down. In the maximum of 15 seconds that it takes to compact the db someone tries to open it. Never happened to me.

Maybe the better thing is to put in code to close the db at midnight and then run compact via Task Scheduler. The command to do so is

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "dblocation" /compact

Autoeng