Darryl Collins
darryl at whittleconsulting.com.au
Wed Dec 7 17:59:26 CST 2011
Alright. That sounds like a possible suspect. I can make a start by unbinding the forms, not hard to do but a bit more coding work. Heh, Hey Tina. Thinking of you right now. I was thinking when I started this - "leaving them bound is fast and easy, but I really should do them unbound like I usually do. naah it will be ok..." :)
Here is a great example of what I am talking about. Got a blank version of this database - no data in any of the table. Been compacted and reopened after deleting the data - copied 40 lines of data (x 2 columns - so 80 fields of simple data data in all) into a table. Get a "out of resources" message. Blah! Oddly it still copies the data in ok, once I press "ok" on the warning msgbox.
*Sigh*. Will start to unbind the buggers and see if that helps - change to a JIT approach instead.
Thanks Doug!
Cheers
D
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Steele
Sent: Thursday, 8 December 2011 10:43 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Advice on A2010....
Each time you open a subform or bound listbox/dropdown, you use up system resources - it doesn't matter how big the underlying tables are. In Access
2003 if I remember correctly, the total number of open recordsets (or
connections?) is something like 256. I once built a cafeteria recipe/menu building screen which had 31 subforms, and each subform had multiple dropdowns in it to select the recipes used. I was so proud of it until I discovered that it wouldn't run! I had to change it from a monthly to a weekly screen before it would work.
Doug
On Wed, Dec 7, 2011 at 3:33 PM, Darryl Collins < darryl at whittleconsulting.com.au> wrote:
> Hi everyone,
>
> Boy, A2010 does some weird stuff.
>
> I have an accdb database. It is on my local drive, it is only me who
> uses it, it is only me who can access it. It has DAO code it uses to
> do stuff with recordsets within the currentDB only. There are no
> external connections. There is no BE / FE setup (doesn't need it).
> This database is tiny (about 7 MB) - doesn't have a lot of data or
> really do any heavy processing at all - basic stuff.
>
> The only thing that is unusual for me is I have one main form, which
> has
> 37 tabs on it which in turn has listboxes and subforms - and the
> subforms are bound to their tables - I would usually use unbound, but
> in this case there is no point. Besides they are all really small datasets.
>
> For weeks this has worked pretty well - then suddenly I started
> getting an error when trying to make design changes "Not opened in
> Exclusive mode - another user is using the database - can't save
> changes" was the gist of it. Now how can that happen? There was some
> suggestion from Google search that it maybe one of the DAO recordsets
> was being left opened and thus Access thinks there is a open
> connection and thus another user(?). I double checked I was closing
> all RS and setting them to nothing when I finished with them.
>
> Nothing seemed to work. After much poking around on Google I found
> this piece of code and added it. It seems to have helped, but I am
> not sure if that is just co-incidence or not. I had done a few tweaks
> and rebuild a couple of modules so hard to say:
>
> "'Initiate Passive Shutdown - do not allow new Users
> CurrentProject.Connection.Properties("Jet OLEDB:Connection Control") = 1"
>
> This is meant to force access to reject any new users to the database.
> I wish I could say with confidence that this was what fixed it. I
> guess I could comment out the code and do some tests, but right now,
> after losing two days, I am just wanting to catch up on the days I have lost.
>
> The other weird issue I get at some point when using this database is
> "You don't have enough resources to perform the operation". WTF?
> Again this will fail on doing something low impact and simple. It is
> like Access hit some sort of limit (again maybe open connections via
> DAO.Recordset(?) - this is where it usually fails when using code to update stuff via DAO).
> But all the DAO connections are all being closed in the code - and
> usually only 1 is open at any one time (a max of 3 in a couple of
> modules) and they are all set to nothing once I have finished with
> them. Besides they are also doing bugger all work. Maybe updating 10
> records out of a total of 50
> - that sort of thing. What 'resources' could possibly be being
> exhausted with that sort of workload? A restart of the app will
> usually fix the problem, but what is going here?
>
> Has anyone else had these issues, and any ideas on what to do about them?
>
> Cheers
> Darryl.
>
> Darryl Collins
> Whittle Consulting Pty Ltd
> Suite 8, 660 Canterbury Rd
> Surrey Hills, VIC, 3127
>
> p: +61 3 9898 3242
> m: +61 418 381 548
> f: +61 3 9898 1855
> e:
> darryl at whittleconsulting.com.au<mailto:darryl at whittleconsulting.com.au
> >
> w: www.whittleconsulting.com.au<http://www.whittleconsulting.com.au/>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com