Compacting FE/BE Database

[DavidB]

I have a standard FE/BE database. I'd like to be able to have a
button on each db that allows the admin to compact the database. I am
using the following code to perform the compact.

Private Sub bCompact_Click( )
On Error GoTo Err_bCompact_Cl ick

CommandBars("Me nu Bar").Controls( "Tools").Contro ls("Database
utilities").Con trols("Compact and repair
database...").a ccDoDefaultActi on

Exit_bCompact_C lick:
Exit Sub

Err_bCompact_Cl ick:
ErrorMessage Me.Name, "bCompact_Click ", Err.Number,
Err.Description
Resume Exit_bCompact_C lick

End Sub


The bCompact button on the FE is on a password protected Admin Menu.
The BE will only be run by the Admin and only when a compact is
needed. thus the bCompact button in on the MainMenu on the BE.

I would like to add two pieces of code as follows...

1) BE - Add code that determines if anyone has the database open and
only performs the compact if no one does.

2) FE - Add code that determines if a compact is in progress and
closes the db if so. (Opinions as to whether this is actually
necessary are welcome. Even at 2-3 times the maximum expected data
storage, a compact should only take a few secodns to perform and all
users are in the immediate vicinity thus word of 'compacting is about
to start' SHOULD be easy to maintain.)


Thanks

[ApexData@gmail.com]

Re: Compacting FE/BE Database

David I believe that most in the group will tell you to put code
togeather seperate from your project (Not in the FE or BE) to 1st test
that the BE is closed (ie .ldb is gone), and if closed is confirmed to
Compact/Repair.

If you are careful and make sure that there are no links to the BE,
then you can in-fact perform a Compact/Repair from the FE. This can
be performed by calling an ExitForm just before exiting the App. In
this form you would use the Timer event to check that the BE is
closed. I say to use the Timer because it may take a second or two
over the network before the .ldb is dropped. At the point that it is
confirmed as closed, than your code would perform the Compact/Repair.
Or, of course you could prompt the user to Compact/Repair.

Greg

[DavidB]

Re: Compacting FE/BE Database

On May 14, 3:42 pm, "ApexD...@gmail .com" <ApexD...@gmail .comwrote:

David I believe that most in the group will tell you to put code
togeather seperate from your project (Not in the FE or BE) to 1st test
that the BE is closed (ie .ldb is gone), and if closed is confirmed to
Compact/Repair.
>
If you are careful and make sure that there are no links to the BE,
then you can in-fact perform a Compact/Repair from the FE. This can
be performed by calling an ExitForm just before exiting the App. In
this form you would use the Timer event to check that the BE is
closed. I say to use the Timer because it may take a second or two
over the network before the .ldb is dropped. At the point that it is
confirmed as closed, than your code would perform the Compact/Repair.
Or, of course you could prompt the user to Compact/Repair.
>
Greg

Greg,
You are saying to make a stand alone compact utility? I could do
that but I fail to see how that addresses the issues I asked about
(especially the issue of a user opening the front end during a compact/
repair.) As far as your comment of "If you are careful and make sure
that there are no links to the BE", if that is the case whats the
point of having a front end and back end in the first place? Perhaps
I misunderstand what you are saying, but normall;y a front end holds
your code, forms, macros, etc and your back end holds the data. The
data is linked into the front end from the back end.

Thanks,
DB

[ApexData@gmail.com]

Re: Compacting FE/BE Database

David

1) BE - Add code that determines if anyone has the database open and
only performs the compact if no one does.

I personally would not have code in the Backend that performs the
Compacting, Because the BE should be closed before compacting!

Most suggest that a seperate utility be created that checks that the
BE is closed, and if so perform the compact. However, If you wish to
Compact from within the App, than the FE is where I do it. You need to
be careful that there are no links to the BE! By this I mean that you
need to CLOSE all the FE FORMs that have links or are Bound to the
tables in the BE. Once all these bound forms are closed, you could
use an Unbound form while still in the FE to handle the Compacting.
By closing all the bound FE Forms, you can release the link to the BE.

For example, Under an EXIT Button you might do something like this:
Private Sub ExitSystem_Clic k() 'EXIT Button
DoCmd.OpenForm "F-EXIT" 'Form that handles the Compacting
DoCmd.Close acForm, "F-MENU" 'Close the Bound Menu form
End Sub

2) FE - Add code that determines if a compact is in progress and
closes the db if so. (Opinions as to whether this is actually
necessary are welcome. Even at 2-3 times the maximum expected data
storage, a compact should only take a few secodns to perform and all
users are in the immediate vicinity thus word of 'compacting is about
to start' SHOULD be easy to maintain.)

A Compact is relatively quick, and I choose not for it to occur
daily. I do not recall if I check if a Compact is in progress.
Probably not. My apps Compact from the FE based on the number of days
that lapse from the last compact. I usually set it to 14 days. This
last compact date is kept in a FE Table. I have an Admin module in the
FE where this is maintained. This works well. I also perform Backups,
Copies, and Compacts from the CheckBoxes that are ticked there. All
this occurs on Exit from the FE App when needed. Of course the Compact
does not occur if a FE is open somewhere.

Hope this helps somewhat!

Greg

[David W. Fenton]

Re: Compacting FE/BE Database

DavidB <jebva@yahoo.co mwrote in
news:1179158680 .938840.173400@ u30g2000hsc.goo glegroups.com:

2) FE - Add code that determines if a compact is in progress and
closes the db if so.

Why? During the compact, the database is opened exclusively and the
end user won't be able to get to the data, anyway.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[DavidB]

Re: Compacting FE/BE Database

On May 14, 8:54 pm, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:

DavidB <j...@yahoo.com wrote innews:11791586 80.938840.17340 0@u30g2000hsc.g ooglegroups.com :
>

2) FE - Add code that determines if a compact is in progress and
closes the db if so.

>
Why? During the compact, the database is opened exclusively and the
end user won't be able to get to the data, anyway.
>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I was thinking that was the case which is (part of the reason) why I
made the remark about comments being welcome.

Thanks!

[DavidB]

Re: Compacting FE/BE Database

On May 14, 5:35 pm, "ApexD...@gmail .com" <ApexD...@gmail .comwrote:

David
>

1) BE - Add code that determines if anyone has the database open and
only performs the compact if no one does.

>
I personally would not have code in the Backend that performs the
Compacting, Because the BE should be closed before compacting!
>
Most suggest that a seperate utility be created that checks that the
BE is closed, and if so perform the compact. However, If you wish to
Compact from within the App, than the FE is where I do it. You need to
be careful that there are no links to the BE! By this I mean that you
need to CLOSE all the FE FORMs that have links or are Bound to the
tables in the BE. Once all these bound forms are closed, you could
use an Unbound form while still in the FE to handle the Compacting.
By closing all the bound FE Forms, you can release the link to the BE.
>
For example, Under an EXIT Button you might do something like this:
Private Sub ExitSystem_Clic k() 'EXIT Button
DoCmd.OpenForm "F-EXIT" 'Form that handles the Compacting
DoCmd.Close acForm, "F-MENU" 'Close the Bound Menu form
End Sub
>

2) FE - Add code that determines if a compact is in progress and
closes the db if so. (Opinions as to whether this is actually
necessary are welcome. Even at 2-3 times the maximum expected data
storage, a compact should only take a few secodns to perform and all
users are in the immediate vicinity thus word of 'compacting is about
to start' SHOULD be easy to maintain.)

>
A Compact is relatively quick, and I choose not for it to occur
daily. I do not recall if I check if a Compact is in progress.
Probably not. My apps Compact from the FE based on the number of days
that lapse from the last compact. I usually set it to 14 days. This
last compact date is kept in a FE Table. I have an Admin module in the
FE where this is maintained. This works well. I also perform Backups,
Copies, and Compacts from the CheckBoxes that are ticked there. All
this occurs on Exit from the FE App when needed. Of course the Compact
does not occur if a FE is open somewhere.
>
Hope this helps somewhat!
>
Greg

Thanks Greg... Your suggestions combined with some of what I already
have in place get me to a palce that is a workable solution for all
parties involved.

[ApexData@gmail.com]

Re: Compacting FE/BE Database

Glad to Help.
Greg