[AccessD] Advice on A2010....

Hi Jim:

Thanks for correcting my answer - my memory didn't serve me well.  The
problem with my mighty recipe database was the control count.

Doug

On Thu, Dec 8, 2011 at 8:49 AM, Jim Dettman <jimdettman at verizon.net> wrote:

>
>  Couple of comments:
>
> 1. In regards to Doug's comments on connections, you really should only
> have
> one with DAO as long as your not using Opendatabase.  Always using
> currentdb(), will use the same connection.  Don't see where your bumping
> into the 255 user limit there.
>
> 2. Access is limited to 2048 table ID's open at one time.  A table ID is
> used for every table and field reference.  A form with 37 tabs is huge and
> I
> would guess that's what your running into.
>
> 3. Access is limited to 754 controls over the life of a form, so watch how
> big you get (note the limit is not 754 - deleting and creating a control
> does not decrement/increment the count, it only increments it).  Surprised
> you didn't run into that one.
>
> 4. If your working in ADO, make sure you use CurrentProject.Connection
> This is Access/Jet's ADO connection for the DB you opened and as long as
> you
> use it, you'll show up as only one user.  If you open your open ADO
> connections, each will count as a user (when opened together) and will
> count
> towards the 255 limit.
>
> HTH,
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
> Sent: Wednesday, December 07, 2011 06:33 PM
> To: Access Developers discussion and problem solving
> (accessd at databaseadvisors.com)
> Subject: [AccessD] Advice on A2010....
>
> 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
>