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.

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).

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Denise" <(E-Mail Removed)> wrote in message
news:184DA5B5-F97B-4B54-B19E-(E-Mail Removed)...
> 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.

On Sat, 11 Apr 2009 13:22:10 -0700, Denise <(E-Mail Removed)>
wrote:

>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.
--

John W. Vinson [MVP]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
news:(E-Mail Removed):

> Compact On Close (which is seldom a good idea, btw)

It's never a good idea. It is useless in a front end and endangers
your data in a back end.

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

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> 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).

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

On 11 Apr 2009 23:53:52 GMT, "David W. Fenton" <(E-Mail Removed)>
wrote:

>John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
>news:(E-Mail Removed):
>
>> 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).

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?

--

John W. Vinson [MVP]

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>>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).
>
>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/

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> 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.

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

"Tony Toews [MVP]" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> 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.

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

On 12 Apr 2009 20:23:14 GMT, "David W. Fenton" <(E-Mail Removed)>
wrote:

>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.
--

John W. Vinson [MVP]