[AccessD] Advice on A2010....

Thu Dec 8 11:03:49 CST 2011

Like the "fast and easy"  function I put in an A97 application that I built years ago which  is still 
in use

Functiion Wait(secs as long) as long
DIm t as Single
t = timer
Do
Doevents
Loop until timer = t + secs
End Function

It worked fine for years until a couple of  months ago when they ran a monthly process late at 
night and the Wait function was in the middle of the loop at midnight :-(

-- 
Stuart

On 8 Dec 2011 at 10:07, Tina Norris Fields wrote:

> Thanks for keeping me company, Darryl.  I'm going to make up a plaque 
> that says something like NEVER, NEVER DO IT THE FAST AND EASY WAY.  
> LATER ON IT IS GOING TO BITE YOU IN THE SIT-DOWN!
> T
> 
> Tina Norris Fields
> tinanfields at torchlake.com
> 231-322-2787
> 
> 
> On 12/7/2011 6:59 PM, Darryl Collins wrote:
> > 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
> >
> >
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 