UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> VBA to Compact & Repair on Last User Logout    
 
   
mvasilevsky
post Dec 17 2004, 04:29 PM
Post #1

UtterAccess Enthusiast
Posts: 69
From: Bay Area, CA



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!
Thanks,
v
Go to the top of the page
 
+
kfield7
post Dec 19 2004, 10:21 PM
Post #2

UtterAccess Guru
Posts: 552
From: Conroe, TX



To see who's loggin in
who's on?
compact and repair
compact
Go to the top of the page
 
+
R. Hicks
post Dec 19 2004, 10:31 PM
Post #3

UA Forum Administrator
Posts: 40,122
From: Birmingham, Alabama USA



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.
Ochoose to compact the database "selectively" when it needs compacting ...
RDH
Go to the top of the page
 
+
kfield7
post Dec 19 2004, 10:56 PM
Post #4

UtterAccess Guru
Posts: 552
From: Conroe, TX



Ricky,
Are you refering to the
Tools->Options->General->check compact on close
feature?
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.
Go to the top of the page
 
+
R. Hicks
post Dec 19 2004, 11:02 PM
Post #5

UA Forum Administrator
Posts: 40,122
From: Birmingham, Alabama USA



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 ..
Oassume that Access will generate an error that could be used to do what is asked ...
Though .. I could be wrong.

RDH
Go to the top of the page
 
+
GroverParkGeorge
post Dec 20 2004, 01:18 AM
Post #6

UA Admin
Posts: 22,773
From: Newcastle, WA



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.

George
Edited by: GroverParkGeorge on Mon Dec 20 1:20:15 EST 2004.
Go to the top of the page
 
+
kfield7
post Dec 20 2004, 04:29 PM
Post #7

UtterAccess Guru
Posts: 552
From: Conroe, TX



Thanks Ricky & George.
Is 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.
Go to the top of the page
 
+
R. Hicks
post Dec 20 2004, 07:59 PM
Post #8

UA Forum Administrator
Posts: 40,122
From: Birmingham, Alabama USA



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 ...

RDH
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st November 2014 - 06:59 PM