[AccessD] Advice on A2010....

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