[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
>