Compacting a Database

[Connie]

Sorry I'm so long replying to this posting. I was away,
but this was a reply to my posting of July 13th from
Douglas J. Steele

Douglam you said:

Are your applications split into a front-end (containing
the queries, forms,
reports, macros and modules) and a back-end (containing
only the tables)? If
so, then there's no need to compact on close: just have a
scheduled process
that compacts the back-end on a regular basis.
_______

Yes the database is split, but if the front end is not
preiodially compacted, its size grows with each use.
Also, I have experienced problems compacting a split back
end, whether the back end is on a server or in a file on
the users computer. Because during the compacting
process the system creates a temporoary copy of the
database and renames it, this has caused problems. The
temporary copy is supposed to disappear after the
compacting is done.

I have experienced situations where the back end is
completely replaced by the temporary copy, keeping its
new name (db1.mdb or the next number in line). Therefore
the front end does not recognize the back end...unless
you rename it to the proper name of course.

We have decided against compacting back ends, but as I
mentioned above, the front end must be compacted for the
reason stated.

If we are missing something and/or there is a better way,
I would appreciate your comments.

Thanks
Connie

 

[Albert D. Kallal]

_______

Yes the database is split, but if the front end is not
preiodially compacted, its size grows with each use.

Well, the FE should be a mde file (this can also reduce increases increase
in size as it "stays" compiled all the time).

Further, you can most certainly set the FE database to compact on close.
This will totally eliminate any file increase in size.

Also, I have experienced problems compacting a split back
end, whether the back end is on a server or in a file on
the users computer.

When compacting a file, the system does NOT have some special knowledge that
it is a back end. All ms-access knows that you have a file.

So, the issue of the database being split, or not split is complete
un-related here. You are simply compacting a file, and there is no "special"
knowledge that ms-access has that his file is some how a back end, or a
front end..it knows nothing about this concept.

the mdb BE end is just a regular data file as far as access is concerned.
How does it know the difference?

Because during the compacting
process the system creates a temporoary copy of the
database and renames it, this has caused problems. The
temporary copy is supposed to disappear after the
compacting is done.

Well, the same problem can occur if you simply compact a file sitting on a
server. If that file is split, or not split, the identical process occurs
when you compact it. So, while you may encounter a problem of compacting a
file on a server, it is NOT a issue of split vs not split.

We have decided against compacting back ends, but as I
mentioned above, the front end must be compacted for the
reason stated.

If we are missing something and/or there is a better way,
I would appreciate your comments.

As mentioned, there is no conceptual difference here when compacting a FE,
or BE file sitting on the server. I would certainly try and compact the back
ends from the actual server, since then you are not dragging the file across
a network, which can be a weak spot.

So, you should continue to compact your back ends frequently. And, there is
absolute ZERO evidence that compacting a back end on server, or compacting a
regular mdb file sitting on the server is more, or less reliable (there is
no difference). Of course, we rarely compact the FE on the server, since
this file is going to be placed on EACH pc. So, this point is kind of moot,
but I must stress that you are simply compacting a file, and a file is a
file is a file....

In fact, since when you split the database, the back end is likely to be
MUCH smaller. So, if I had to bet here, then I would have say if anything,
compacting a back end on a server across a network is for sure MORE reliable
then compacting a non split application sitting on a server (this is almost
certain, since the BE will be MUCH smaller).

So, really, at the end of the day here:

You need to compact on a regular bases
Why not set the front end to compact on close (but, it still should be a
mde file).

You need to schedule some time to compact the back ends on the server,
and if possible, that should be done ON the actual server, as then you don't
drag the file across a possible network. Another approach is to copy the
file to a local pc, compact it,a nd then copy back to the server

 

[Van T. Dinh]

See Tony Toews' tips on bloating of Front-Ends:

<http://www.granite.ab.ca/access/bloatfe.htm>

For the Back-End, you should only attempt to Compact when
there are no other users using the database, i.e. no other
users linking to the Back-End Tables.

HTH
Van T. Dinh
MVP (Access)

 

[Connie]

I am in agreement with back end compating...thanks for
the info

 

[Connie]

Thanks Albert, but pretty much everything you said, I
already know regarding FE's and BE's. Basically I was
repsonding to someone's suggestion.

My origianl question is this...and maybe you can
help...when our database is compacting it stalls about 1
3rd into the process, holds, then continues with the
compact. What could be causing this and is there a fix?

Thanks.

 

[George Nicholson]

Connie:

(Pending a more authoritative response because this is purely anecdotal

I have seen that behaviour when the BE had one table that was
*substantially* larger than any other table (larger than all others
combined). I assume that the status bar simply reflects the % of objects
processed and that the "stall" correlated to that huge table being
processed. I've only noticed this when there was an elephant of a table
within a 350+ MB file, so I just assumed that the 2 are related.

So, I don't think the compacting actually "stalls", it is just working on a
big, single chunk and the status bar won't reflect a change until that chunk
is complete.

Package & Deployment wizard installation routines behave similiarly with
this file: it appears to hang at either 10% or 90%, but it will jump to
completion if you simply leave it alone long enough.

 

[Tony Toews]

(Pending a more authoritative response because this is purely anecdotal

I have seen that behaviour when the BE had one table that was
*substantially* larger than any other table (larger than all others
combined). I assume that the status bar simply reflects the % of objects
processed and that the "stall" correlated to that huge table being
processed. I've only noticed this when there was an elephant of a table
within a 350+ MB file, so I just assumed that the 2 are related.

So, I don't think the compacting actually "stalls", it is just working on a
big, single chunk and the status bar won't reflect a change until that chunk
is complete.

I'd agree with this anecdotal statement as I've seen the same myself.
And you answer makes sense. The status bar is only being updated when
a table has completed compacting.

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

 

[Connie]

Thanks everyone!!
It has all helped.