RE: Compacting MDB help

Hi

I can see now exactly what you're looking to do. I can sort it for you but
suggest you email your email address to me at barry.wycherley@xxxxxxxxxxxxxxxx

I'll knock up an external FECompact.mdb for you, together with instructions
on how to activate it from your FrontEnd. It can work without user
intervention. i.e. users choose their new database to link to, get told to
'go get coffee' and come back to their linked and compacted FrontEnd.

BW

"Pendragon" wrote:

Users of this system frequently change backend database selections during the
course of a day. Even switching databases three or four times causes the
application to grow in size considerably, and then only even that happens is
refreshing links on the tables - there is no data upload or transfer from
backend to frontend. From a user efficiency and process point of view, it is
not practical to have staffpersons open another database in order to switch
backend mdb files.

When the front end opens, it goes directly to the main switchboard. The
user either goes into one of the given modules for the client database that
is currently connected, or clicks on the cbo to change databases. Therefore
it is necessary to somehow check the file size, activate an external database
to run a compact procedure on the current front end, close the external
database and return to the front end.

If this is something to can assist me in setting up, I would be grateful.

"BeWyched" wrote:

Hi

Thinking this through, the only reason why you would need to Compact the
FrontEnd is if you are moving data into it from the back-end databases which
is subsequently deleted. This will cause the database to grow so Compacting
is necessary.

However, it is only the data tables that grow, not the forms etc. So, why
not split off the tables from your FrontEnd. You will then have a new
FrontEnd, a new FrontBackEnd which is permanently linked to the FrontEnd and
the back-ends which change. IYou will not need to change any coding if you do
this.

The benefit is that the a), b) and c) plus the Compacting coding will now
work from the FrontEnd as it will operate on the FrontBackEnd which is now a
seperate database and hence, can be compacted.

Just a thought!

BW

"Pendragon" wrote:

Okay, I have an mdb file called FECompact.mdb. The autoexec macro opens a
simple form with a label on it telling the user to go get some coffee while
the FrontEnd.mdb (not its real name, btw) compacts.

Here's what I need.

1) The FrontEnd.mdb's main switchboard has a cbo to switch back end
databases. As said before, the cbo_AfterUpdate refreshes the links. When
this is complete, the cbo_AfterUpdate lastly calls the function
fCheckFileSize. This function checks the file size of the FrontEnd.mdb file.
If the file size is greater than X, then I need code to open FECompact.mdb.

2) FECompact.mdb runs the few lines of code to compact the FrontEnd.mdb
file. When the compact is complete, I need code to close FECompact.mdb. My
thought is that this should be placed in the autoexec of FrontEnd.mdb and
should check to see if FECompact.mdb is open, and if so, close it; if not
open, then simply open the Main Switchboard as usual.

Question: Is the code of the FrontEnd.mdb executed and closed (the
AfterUpdate process) BEFORE the autoexec of FECompact.mdb (and subsequent
form OnOpen code to compact the FrontEnd.mdb) is initialized, OR does the
AfterUpdate process not hit the Exit Sub/End Sub line until the FECompact.mdb
process is complete?

Many thanks.

"BeWyched" wrote:

Hi

Sorry, I misunderstood your intention. My interpretation of your initial
posting was that you wanted to Compact the back-end databases, not the
front-end?

I'm quite certain that you can't Compact an open database from within itself
using VBA as the process of Compacting requires the database to close - this
is why you're getting the error messages. When you do this manually through
the menu bar it closes the database, carries out the Compact, then re-opens
it.

The obvious solution is to create a new database which carries out your a),
b) and c) on your front-end with the Compact VBA between b) and c). Your a),
b) and c) will need to set the DB object as the front-end:

Set db = OpenDatabase("\path\frontend.mdb")
rather then,
Set db = CurrentDB

then programatically open your front-end and go to your switchboard form:

Set appAcc = CreateObject("Access.Application")
appAcc.OpenCurrentDatabase "\path\frontend.mdb"
appAcc.DoCmd.OpenForm "name of switchboard form"

No reason why this shouldn't work.

Good luck.

BW

"Pendragon" wrote:

No go.

There was not an "old" .ldb file, just the .ldb file that exists because the
front end database is open. Obviously, that cannot be deleted while the
front end database is still running.

It seems that this process should work, but this is now getting frustrating.
:-) Do I need to start thinking about opening another access application
whose sole purpose is to compact the front end database?

"BeWyched" wrote:

Check to see if there is an old .ldb file open on 'John's' machine. It will
be in the same folder as the database file. If so, delete it.

"Pendragon" wrote:

BW,

Thanks. I set your code in the Compact Function in the If....Then statement
(which checks to see if the database size is above a certain limit). I am
getting the ultra annoying exclusive access message - something like "You
attempted to open a database that is already opened exclusively by John on
machine JohnS" blah blah blah.

I looked at Tools Options and Access is set to open exlusive. I changed it
to open shared, closed and reopened my application, and still get the same
message.

Hints/ideas?

"BeWyched" wrote:

Hi

I'd use the CompactRepair method to create a temporary compacted file. Then
delete (or better archive) the old file and re-name the temporary file as
the database file.

e.g. If your database is db1.mdb in the c:\ drive then the coding is:

DBEngine.CompactDatabase "c:\db1.mdb", "c:\temp.mdb"
Kill "c:\db1.mdb"
Name "c:\temp.mdb" As "c:\db1.mdb"

Only use the Kill command if you are able to recover the data if things go
wrong. If not then best to archive the original file by, say, substituting
the Kill command with:

Name "c:\db1.mdb" as "c:\db1-" & Date & ".mdb"

You could then run a loop to delete any archived files after a certain,
safe, period.

The coding goes between b) and c).

I have an application that uses this method. It compacts a huge (100Mb) file
daily and has done so for some 5 years without any hitches.

Good luck.

BW


"Pendragon" wrote:

Access 03/WinXP

I've read through a dozen of the postings regarding compacting a database
and have tried to use the CommandBars("Menu Bar").Controls("Tools")....etc.
to compact the database. I still run into the error about trying to compact
while running VB code.

The setup:

Front end database tied to multiple back end databases. Back end is
selected through a combo box on main switchboard. The process:

The cbo_AfterUpdate event procedure:
a) closes all open forms.
b) Calls a function which refreshes all of the table links to the selected
back end database.
c) Re-opens the main switchboard.

I have a function that checks the file size of the database. An If...Then
statement controls whether or not to run the compact.

Where is the appropriate point to call the Compact Function? Also, when a
database compacts, is the AutoExec macro re-initialized on completion?

Initially, I had the Compact Function in between b) and c) above, except it
only prompted the user to manually run through the toolbar commands. I
thought that in using CommandBars("Menu Bar").Controls("Tools")....etc. I
might accomplish the automatically. Am I running into a problem because this
command line is in an If...Then statement?

Your direction is greatly appreciated.
.

Relevant Pages

  • Switchboard does not open when the dbase is opened
    ... You need to open the database in design then go to Tools, ... >database, the switchboard does not open, it opens to the ... >Private Sub Form_Open ...
    (microsoft.public.access.forms)
  • Re: Probs accessing database for users of newly developed Superuse
    ... Startup is set to open the Switchboard form on opening. ... I think I used the Switchboard manager to create the Switchboard. ... I've included the code for when the form opens below. ... ' Minimize the database window and initialize the form. ...
    (microsoft.public.access.security)
  • Re: Probs accessing database for users of newly developed Superuse
    ... "Joan Wild" wrote: ... > I think I used the Switchboard manager to create the Switchboard. ... I've included the code for when the form opens below. ... > ' Minimize the database window and initialize the form. ...
    (microsoft.public.access.security)
  • Re: Repair corrupted forms
    ... Compact the database to get rid of this junk: ... Still in the code window, choose Compile from the Debug menu. ... however having tried Access 2002 it opens ok. ...
    (microsoft.public.access.forms)
  • Re: Compact Database
    ... procedure to compact your main database file, ... put macro or code in your main database to open this compact-triggering ... >>in a Macro it opens a dialogue box ...
    (microsoft.public.access.macros)
Loading