Dec 17 2004, 04:29 PM
Does anyone have VBA code example for doing an automatic compact and repair on a multiuser application when the last user exits the app? I'd like the code to check everytime a users closes the db for any other users logged on, and if no other users logged on to do a compact and repair.
Any help would be appreciated!
Dec 19 2004, 10:21 PM
To see who's loggin in who's on?
compact and repair compact
Dec 19 2004, 10:31 PM
I never use the compact on close feature .. but I suspect that if it is implemented in a multi-user environment that Access will generate an error that can be trapped if other users are logged in to the app.
If this proves to be true .. then you can trap the error and exit the routine if the error occurs.
If there is no error .. then everyone must have logged out of the database .. so execute the code to compact the database at that point ...
This is only "airware" .. as I said .. I do not use this feature.
I choose to compact the database "selectively" when it needs compacting ...
Dec 19 2004, 10:56 PM
are you refering to the
Tools->Options->General->check compact on close
The help file says in a multi-user environment it won't do anything unless the last user closes the database. I recently used this on a shared database that was always bloating up ( i think because the user can use different back-ends ). No problems, it seems to do all the work in the background.
Dec 19 2004, 11:02 PM
Nope ... that is not what I was referring to ...
If the compact and repair is invoked on a machine "with code" where other users are currently logged on to ..
I assume that Access will generate an error that could be used to do what is asked ...
Though .. I could be wrong.
Dec 20 2004, 01:18 AM
I have had very bad experience trying use compact on close in a shared environment. While the compact completes successfully for the LAST person out, that is not necessarily the case for other users who log out earlier. WHat tends to happen is that the compact process kicks off, but can't complete because of the other logged in user. It then leaves behind the temp MDB it tried to create for the compact. You can end up with a whole series of "db1.mdb", "db2.mdb", "db3.mdb", etc. in the shared folder. And Access doesn't give notice that this is happening, so it does appear that everything is hunky dorey.
On one network which is notoriously unstable, we also had a coule of cases where the compact on close process kicked off and didn't complete, leaving the mdb corrupted.
In short, I would not use this feature on a shared mdb.
That's my 2 cents.
Edited by: GroverParkGeorge on Mon Dec 20 1:20:15 EST 2004.
Dec 20 2004, 04:29 PM
Thanks Ricky & George.
As with MV, I have another project in the works where I thought compact on close would be useful, and it makes sense to check for other users first, but provide error trapping anyway, just in case someone opens the database in the time between when the Access determines no one is on and the time when it actually can execute the compaction.
In the instance I cited above, there have only been 2 users concurrent. The upcoming project may have several users (but with individual front ends). So while I was thinking automating back end compaction might be good, I don't think I want to select the automatic feature from the options menu. It sounds like it should still work via code if the code checks for other users first.
Dec 20 2004, 07:59 PM
As George pointed out .. compacting a database that is not on the local machine is a catastrophe waiting to happen ...
A database should "always" be compacted from the machine where the file resides ...
If there is a hiccup in the network ... the result could very well be a corrupted database that can not be recovered ...
In a multi-user environment .. if the BE file is not on the local machine ...
It should be copied to the local machine, compacted .. then returned to the machine or server where it resides.
Doing it any other way is an invitation to a major problem ...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here