key is not unique in collection
in [Help]
|
From: Phil on 23 Jul 2010 18:48 On 23/07/2010 17:56:15, "Stuart McCall" wrote: > "Phil" <phil(a)stantonfamily.co.uk> wrote in message > news:b_2dnTpDbP7AHdTRnZ2dnUVZ8kWdnZ2d(a)brightview.co.uk... >> On 23/07/2010 12:00:51, "Stuart McCall" wrote: >>> "Phil" <phil(a)stantonfamily.co.uk> wrote in message >>> news:4M-dndKuE_si7dTRnZ2dnUVZ8uidnZ2d(a)brightview.co.uk... >>>>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 >>> >>> 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) >>> >>> >> >> 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 > > Do the treeview's lines still vanish? If not, then the 2nd error is > unlikely to be a problem with the treeview (I think). 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 > >> >> 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 > > 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?) > >> Have recenly rebuilt the Db but have always had this problem. 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. >> Phil >> > >
From: Phil on 24 Jul 2010 04:06 On 23/07/2010 23:48:11, "Phil" wrote: > On 23/07/2010 17:56:15, "Stuart McCall" wrote: >> "Phil" <phil(a)stantonfamily.co.uk> wrote in message >> news:b_2dnTpDbP7AHdTRnZ2dnUVZ8kWdnZ2d(a)brightview.co.uk... >>> On 23/07/2010 12:00:51, "Stuart McCall" wrote: >>>> "Phil" <phil(a)stantonfamily.co.uk> wrote in message >>>> news:4M-dndKuE_si7dTRnZ2dnUVZ8uidnZ2d(a)brightview.co.uk... >>>>>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 >>>> >>>> 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) >>>> >>>> >>> >>> 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 >> >> Do the treeview's lines still vanish? If not, then the 2nd error is >> unlikely to be a problem with the treeview (I think). > > 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 > >>> >>> 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 >> >> 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?) >> >>> > Have recenly rebuilt the Db but have always had this problem. > 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. > >>> Phil >>> >> >> > 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
From: Stuart McCall on 24 Jul 2010 04:31 <snip> > 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 Glad you're up & running. I'm going to file that one under "things that make you go hmm".
From: David W. Fenton on 24 Jul 2010 16:53 "Phil" <phil(a)stantonfamily.co.uk> wrote in news:6fedncHuj8twi9fRnZ2dnUVZ8u6dnZ2d(a)brightview.co.uk: > On 23/07/2010 20:36:40, "David W. Fenton" wrote: >> "Phil" <phil(a)stantonfamily.co.uk> wrote in >> news:b_2dnTpDbP7AHdTRnZ2dnUVZ8kWdnZ2d(a)brightview.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. -- David W. Fenton http://www.dfenton.com/ contact via website only http://www.dfenton.com/DFA/
From: Phil on 25 Jul 2010 05:21 On 24/07/2010 21:53:25, "David W. Fenton" wrote: > "Phil" <phil(a)stantonfamily.co.uk> wrote in > news:6fedncHuj8twi9fRnZ2dnUVZ8u6dnZ2d(a)brightview.co.uk: > >> On 23/07/2010 20:36:40, "David W. Fenton" wrote: >>> "Phil" <phil(a)stantonfamily.co.uk> wrote in >>> news:b_2dnTpDbP7AHdTRnZ2dnUVZ8kWdnZ2d(a)brightview.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
First
|
Prev
|
Pages: 1 2 Prev: key is not unique in collection Next: Multiple queries into a single report |