Compacting and Repairing

[Dennis]

Is there a way to automate, perhaps with a macro or
vbscript, running "Compact and Repair" from the Tools Menu
in my Access 2000 database?

There is a table that I'm going to be appending to, and
deleting from quite often (no, a Make-table query won't
work in this instance)and I don't want the database to
expand out to where it won't work anymore. To keep things
running good, I'd like to just automatically compact and
repair, but do so automatically.

 

[Nikos Yannacopoulos]

Dennis,

You're not specifying whther this is a monolithic, single user database, or
a split multi user one. In the former case, all you need to do is go Tools >
Options, tab General, and select the Compact On Close option. This will
result in your database being compacted every time you close it, which I
suppose would do the job just fine.

If, on the other hand, you need to compact a back end on some server, then
you could use the Windows Scheduler on a server/PC that has Access
installed, to run a small procedure in VB code, either in a VB .exe or even
in a separate .mdb built just to host the code.

Which is your case? Need more help with the code?

HTH,
Nikos

 

[Guest]

Nikos -

I have a similar problem to Dennis. I'm designing a multi-user database for
use in a national corporate environment. Most of it's uses will be in
locations other than where I am - and I'm going to operate on the assumption
that my users don't know the first thing about Access. (thus far they've
been plenty confused by most of the "normal" aspects of Access - like the db
window) This being the case - I want as many things to be automated, or with
limited input from the user - including maintenance things like compacting
the back end. This database has records that are added and deleted on a
daily basis, and the quantity could vary from 1 or 2 up to 40-50 per day.

What I WANT is some way for my front end databases to look at the date -
look at the last time it compacted the back end, and if it's more than x days
difference, carry out the compact procedure. (I don't mind having a table
that tracks compacting - or something like that). I have similar code to
determine if the back end needs to import records from an external source, so
getting the procedure to fire isn't a problem - I just haven't a clue how to
get one database to compact another.

Thoughts?

Thanks!
Amanda

 

[Steve Schapel]

Amanda,

Good idea to have a simple one-record "holder" table to store the date
of the last compact.

Many Access applications have a form, like a switchboard/menu form,
which is always open whenever the application is open. If so, the
Unload event of this form is a good place to manage this. Here's an
example of code that you might be able to adapt...

Dim BEPath As String
If DLookup("[LastCompact]","CompactDateHolder") < (Date - 7) Then
BEPath = CurrentDb().TableDefs("OneOfYourTables").Connect
BEPath = Mid(BEPath, 11)
BEPath = Left(BEPath, Len(BEPath) - 9)
If Len(Dir(BEPath & "SR_bu.mdb")) Then
Kill BEPath & "SR_bu.mdb"
End If
If Len(Dir(BEPath & "SR_be.ldb")) Then
'do nothing
Else
DBEngine.CompactDatabase BEPath & "SR_be.mdb", BEPath &
"SR_bu.mdb"
Kill BEPath & "SR_be.mdb"
Name BEPath & "SR_bu.mdb" As BEPath & "SR_be.mdb"
End If
End If
DoCmd.Close acForm, Me.Name
DoCmd.Quit

Obviously, you would substitute the name of your own data file.

 

[Guest]

Steve - Thank you! I am using a switchboard in my program - and I have a
better idea of how to carry out the process now - though I think I may need
a different event to drive everything. It's more than likely that someone
else may be using the back end when the first front end user of the day
closes the program. (Or else code error handling for the front end to take
care of "what happens when the back end isn't available") ... I can just see
someone trying to enter data - and someone else trying to close and compact
at the same time... KaBoom!

*thinks some more*

Amanda

Amanda,

Good idea to have a simple one-record "holder" table to store the date
of the last compact.

Many Access applications have a form, like a switchboard/menu form,
which is always open whenever the application is open. If so, the
Unload event of this form is a good place to manage this. Here's an
example of code that you might be able to adapt...

Dim BEPath As String
If DLookup("[LastCompact]","CompactDateHolder") < (Date - 7) Then
BEPath = CurrentDb().TableDefs("OneOfYourTables").Connect
BEPath = Mid(BEPath, 11)
BEPath = Left(BEPath, Len(BEPath) - 9)
If Len(Dir(BEPath & "SR_bu.mdb")) Then
Kill BEPath & "SR_bu.mdb"
End If
If Len(Dir(BEPath & "SR_be.ldb")) Then
'do nothing
Else
DBEngine.CompactDatabase BEPath & "SR_be.mdb", BEPath &
"SR_bu.mdb"
Kill BEPath & "SR_be.mdb"
Name BEPath & "SR_bu.mdb" As BEPath & "SR_be.mdb"
End If
End If
DoCmd.Close acForm, Me.Name
DoCmd.Quit

Obviously, you would substitute the name of your own data file.

--
Steve Schapel, Microsoft Access MVP

Nikos -

I have a similar problem to Dennis. I'm designing a multi-user database for
use in a national corporate environment. Most of it's uses will be in
locations other than where I am - and I'm going to operate on the assumption
that my users don't know the first thing about Access. (thus far they've
been plenty confused by most of the "normal" aspects of Access - like the db
window) This being the case - I want as many things to be automated, or with
limited input from the user - including maintenance things like compacting
the back end. This database has records that are added and deleted on a
daily basis, and the quantity could vary from 1 or 2 up to 40-50 per day.

What I WANT is some way for my front end databases to look at the date -
look at the last time it compacted the back end, and if it's more than x days
difference, carry out the compact procedure. (I don't mind having a table
that tracks compacting - or something like that). I have similar code to
determine if the back end needs to import records from an external source, so
getting the procedure to fire isn't a problem - I just haven't a clue how to
get one database to compact another.

Thoughts?

Thanks!
Amanda

 

[Steve Schapel]

Amanda,

The concern you raise is the reason for the line in the code...
If Len(Dir(BEPath & "SR_be.ldb")) Then
'do nothing
In other words, if the backend file is in use, then don't proceed with
the compact.