[AccessD] Advice on A2010....

 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