key is not unique in collection

#11

On 24/07/2010 21:53:25, "David W. Fenton" wrote:

Quote:

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:6fedncHuj8twi9fRnZ2dnUVZ8u6dnZ2d (AT) brightview (DOT) co.uk:

On 23/07/2010 20:36:40, "David W. Fenton" wrote:
"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:b_2dnTpDbP7AHdTRnZ2dnUVZ8kWdnZ2d (AT) brightview (DOT) co.uk:

I know it's bad practice, but I set the Db to compactg on exit.
The Db is a BE only, so why it grows, I'm not sure but it does.
The Application.Quit does not allow the compact to run whilst
the RunCommand acCmdExit does. If I use the Application.Quit I
get no error messages however

There is simply no reason for compact on close. Lose it.

David, Any idea what makes it grow?

The most likely reason is that you're treating your back end like a
temp database, i.e., inserting and deleting data that is transient
and should be stored in tables in a temp database rather than in the
back end or front end.

Have opeb the AccDb 3 times and openad a couple of forms.
Sizes were 17928KB (After compact) 18008KB, 18080.

Similarly withe the AccDE sizes were 18816, 18940, 19000. 19008

Uh, why would you care about such small changes in size?

In a properly designed front end, the only thing that I can think of
that will cause it to grow compiling queries. But that will grow
only to a certain point, and once they are all compiled, it won't
grow any more. Constant compaction will cause all those compile
plans to be repeatedly discarded and recreated, so that's another
reason not to compact regularly.

In my opinion, a properly designed front end should never need to be
compacted at all. A new one will grow somewhat for a while and then
stop growing. The only reason to compact is if you need to recompute
table statistics and recompile queries, such as when your dataset
grows extensively (if a table grows from 1000 records to 100,000,
then a compact of the front end is a really good idea, as query
plans based on a 1000-record table could be completely inefficient
with a 100K-record table), but that is something that would likely
need to be done only very occasionally. And the easiest way to
accomplish that is to use something like Tony Toews's AutoFE
Updater, and when the front ends need to be compacted, just push out
an update to everybody, even if there are no actually changes to
your application.

Thanks David.

Compact is off, and I will watch the file size with interest.

Phil

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

key is not unique in collection

comp.databases.ms-access comp.databases.ms-access

Re: key is not unique in collection - 07-25-2010 , 04:21 AM