compact and repair when database is split

D

Denise

If the database is split into a front end and back end, will the database
automatically compact and repair backend when closed if the option is
selected and the user is running from front end? Would I check run compact
and repair in the backend and frontend database options setting? Would that
work?
Thanks.
 
D

Douglas J. Steele

Compact On Close (which is seldom a good idea, btw) will only compact the
front-end. Checking it on the back-end database won't do anything, because
you aren't actually opening it in Access. You can, however, use the
CompactDatabase method of the Database object to compact the back-end from
the front-end, provided no connections exist to the back-end (in other
words, you can't be accessing any data from the back-end at the time).
 
J

John W. Vinson

If the database is split into a front end and back end, will the database
automatically compact and repair backend when closed if the option is
selected and the user is running from front end? Would I check run compact
and repair in the backend and frontend database options setting? Would that
work?
Thanks.

It's usually neither necessary nor a good idea to compact the frontend (which
is all that will happen if you have Compact on Close set); it's simpler and
safer to simply replace the frontend with a fresh copy as needed if it bloats
due to temp tables, big queries, or other issues.

The backend will not "compact on close" (since you don't open it, you don't
close it either); it should be backed up and compacted [IN THAT ORDER!!!!]
manually, while all users are kicked out, and only when needed (doubled in
size is a good guideline). Most backends IME don't really need much
compaction; the only reason they might is if you routinely add and delete lots
of records.
 
D

David W. Fenton

Most backends IME don't really need much
compaction; the only reason they might is if you routinely add and
delete lots of records.

I would suggest that if your app adds large numbers of records, a
compact can be helpful, as it will rewrite the table in PK order,
and will defragement the PK index. But that will only have a
noticeable effect in extremely large tables (>100K records) and with
large numbers of inserts (I would guess somewhere in the
neighborhood of 1000 per day).
 
J

John W. Vinson

I would suggest that if your app adds large numbers of records, a
compact can be helpful, as it will rewrite the table in PK order,
and will defragement the PK index. But that will only have a
noticeable effect in extremely large tables (>100K records) and with
large numbers of inserts (I would guess somewhere in the
neighborhood of 1000 per day).

Frequent and large deletes would qualify too... however, that suggests temp
tables or at least temporary data, which should be (as you have suggested) in
a separate backend created using the CreateDatabase method as needed.

If there are very few deletes and most primary keys are sequential autonumbers
I'd say that compaction should be very rarely needed at all; would you agree,
David?
 
T

Tony Toews [MVP]

John W. Vinson said:
If there are very few deletes and most primary keys are sequential autonumbers
I'd say that compaction should be very rarely needed at all; would you agree,
David?

I have a client with 800K records in a daily labour transactions table
with the MDB size at 300 Mb. They will enter 800 records daily on
weekends in summer with 1600 records per day during the week also in
summer.

They experience an interesting problem during the busy months when the
payroll person needs to run weekly reports. A few reports lock up,
or rather, run forever. Once the sys admin compacts the database
the reports run in a reasonable length of time.

We need to upsize that database to SQL Server.. Of course we won't be
moving the FE to ADP despite one individuals obsession on that topic.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

David W. Fenton

If there are very few deletes and most primary keys are sequential
autonumbers I'd say that compaction should be very rarely needed
at all; would you agree, David?

Absolutely. But I would also say that it really needs to be
scheduled to run automatically, or people will forget to do it. It
can be done with a VBScript and the scheduler on your server. A
script that backs up the existing MDB (using a new filename derived
from the date) and compacts the MDB is pretty trivial to write. With
the backups, I think there's little danger of running it before the
database actually needs it. In a low-churn scenario, I think I'd
schedule it to run at least once a week.

Naturally, there are all the issues with users leaving the app open
and locking, etc., so if you do this, you need to have auto-logout
implemented in your app. I always coordinate the settings I use for
that with the backup/compact schedule so as to not annoy users any
more than necessary.
 
D

David W. Fenton

I have a client with 800K records in a daily labour transactions
table with the MDB size at 300 Mb. They will enter 800 records
daily on weekends in summer with 1600 records per day during the
week also in summer.

They experience an interesting problem during the busy months when
the payroll person needs to run weekly reports. A few reports
lock up, or rather, run forever. Once the sys admin compacts
the database the reports run in a reasonable length of time.

Sounds like a case where the fragmentation causes the data retrieval
for the reports to cross a threshold. Interesting case, and exactly
the level of activity that would cause me to suggest backup/compact
on a daily basis.
We need to upsize that database to SQL Server.

Sure, but it's probably running pretty well when you compact, right?
I know I've always been amazed at exactly how well Jet performs
under high loads -- it can go on problem-free for years after you've
crossed into upsizing territory.
 
J

John W. Vinson

A
script that backs up the existing MDB (using a new filename derived
from the date) and compacts the MDB is pretty trivial to write. With
the backups, I think there's little danger of running it before the
database actually needs it. In a low-churn scenario, I think I'd
schedule it to run at least once a week.

Pretty much what's running on two of my clients' installations.
 
T

Tony Toews [MVP]

David W. Fenton said:
Sounds like a case where the fragmentation causes the data retrieval
for the reports to cross a threshold. Interesting case, and exactly
the level of activity that would cause me to suggest backup/compact
on a daily basis.

I think that even if he compacts it in the morning that the 1500
records are enough to require another compacting.
Sure, but it's probably running pretty well when you compact, right?
I know I've always been amazed at exactly how well Jet performs
under high loads -- it can go on problem-free for years after you've
crossed into upsizing territory.

Oh yes, it does perform quite well. Most of the other users from
nearby provinces come in via Terminal Server and it works well.
There are some very interesting techniques we are doing from a UI
perspective that I'd like to document. Trouble is it would take me
several days of solid work to do it justice.

For example the unbound time sheet entry form has about 5,000 lines of
code behind it validating the data. You can enter up to three
regular, overtime and/or double time transactions on the one form with
all the other data being identical. Then standard "add-on"
transactions are also created of which there could be 5 or 6 on each
transaction.

Then we have a popup form that lists all the just added transactions
so you can go back and edit them in a new copy of the unbound data
entry form just to do a bit of cleanup. All the while leaving the
original data entry open.

Etc, etc, etc.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top