RE: Compacting MDB help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

  • RE: Compacting MDB help
    ... 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). ... Only use the Kill command if you are able to recover the data if things go ... Where is the appropriate point to call the Compact Function? ...
    (microsoft.public.access.modulesdaovba)
  • RE: Compacting MDB help
    ... The autoexec macro opens a ... The FrontEnd.mdb's main switchboard has a cbo to switch back end ... FECompact.mdb runs the few lines of code to compact the FrontEnd.mdb ... I'm quite certain that you can't Compact an open database from within itself ...
    (microsoft.public.access.modulesdaovba)
  • RE: Compacting MDB help
    ... This will cause the database to grow so Compacting ... not split off the tables from your FrontEnd. ... The FrontEnd.mdb's main switchboard has a cbo to switch back end ... FECompact.mdb runs the few lines of code to compact the FrontEnd.mdb ...
    (microsoft.public.access.modulesdaovba)
  • RE: Compacting MDB help
    ... not practical to have staffpersons open another database in order to switch ... When the front end opens, it goes directly to the main switchboard. ... the only reason why you would need to Compact the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access crashes when opening form
    ... Allen Browne - Microsoft MVP. ... You were correct in suspecting SP3. ... I've also had a problem with "compact on close" not working (it ... database works correctly there. ...
    (microsoft.public.access.forms)