[AccessD] Advice on A2010....

Aaah, I dunno.  It is sooo damn random.    Sometimes the same database will function for hours without issue.  This morning, it is it misbehaving almost immediately.  Restarted the app and now it is purring along like a little kitten - no errors or anything.  Sheesh.   I don't mind things not working, but it is so much easier to find the problem when there are consistent symptoms.  It is the seemingly randomness that does my head in.  Just when I get relaxed about it, it will fail.   



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