I am using a treeview type menu system on an Access 2010 Db. There is a close
button on the menuform which runs RunCommand acCmdExit. All works normally on
the AccDb

If I create an AccDe from this, all works OK the first time I close the
database. On subsequent closures I always get the message "Key is not unique
in collection", then a further message "This key is already associated with
an element of this collection". The lines on the treeview control vanish. The
error appears to occurr after the unload event, but before the close event.

Then the Db closes OK.

Any bright ideas please.

Thanks

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Not sure why you're getting the error, and only in those particular
circumstances, but you could try a workaround. Try putting:

Treeview1.Nodes.Clear

before you quit the app. After all, if the form is closing you don't need
the Nodes collection any more. Also you could try using Application.Quit
instead of the RunCommand statement.

(replace Treeview1 with your control's name)

On 23/07/2010 12:00:51, "Stuart McCall" wrote:
Thanks for coming back.
The Treeview1.Nodes.Clear stops the first error message, but not the "This
key is already associated with an element of this collection" error

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

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Do the treeview's lines still vanish? If not, then the 2nd error is unlikely
to be a problem with the treeview (I think).

Well I didn't know that Application.Quit stopped an auto-compact. Hmm.

Incidentally, your BE shouldn't be growing like that, unless you're running
lots of delete and append queries. I'd be tempted to create a new db and
import everything into it from your BE. Can't hurt, and it might clear up
the problem.

(can you tell I'm guessing?)

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

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

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 23/07/2010 20:36:40, "David W. Fenton" wrote:
David, Any idea what makes it grow?
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
Phil

On 23/07/2010 17:56:15, "Stuart McCall" wrote:
After your suggestion of .Nodes.Clear, I am only left with 2 entries on the
Treeview control. The lines still connect these 2 when the err4or message
shows up >
It might?? be caused by using a program called "ShrinkerStretcher" from
Peter's Software which the controls and fonts on forms as you change the size
of the form. This does add and delete a temporary table, but I still get the
growth problem even if I don't use the ShrinkerStretcher routine. On th other
hand it might gave something to do with using a ImageListCtrl to hold the
icons I use for the treeview control, but they are constant. Never have
worked out when they are loaded.

On 23/07/2010 23:48:11, "Phil" wrote:
I think I have cured the problem. My menu opens another hidden form that has
the ImageList control on it that holds the icons displayed in the Treeview.
If I close that, the problem ssems to have vanished.
Odd though that the 2 errors were only apparent in the AccDe, not the AccDb

Thaks for your help

Phil

<snip>
Glad you're up & running. I'm going to file that one under "things that make
you go hmm".

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

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.

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.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/