Darryl Collins
darryl at whittleconsulting.com.au
Thu Dec 8 17:52:53 CST 2011
Jim,
Many thanks. Yep, Point 1 was initially my main concern with using 37 tabs on a single form. I was aware of the control limit on forms and have hit it many times in the past, but usually due to the issue I mentioned in the other post (control count not adjusting when controls are deleted etc).
I feel point 2 is probably what is happening. 2048 sound like a big number, but if every listbox is bound then you can gobble them up rather quickly. I need to leave some of the subforms bound as I am using them as datasheet views. However, yesterday I bit the bullet and did what I should have done from day 1 and converted all of the listboxes to Value lists only (waves at Tina ;)).
Whilst I was running out of enthusiasm with the huge shebang and I need to test it a lot more, the database didn't seem to have any more issues (fingers crossed) and using value lists load and respond a darn site faster anyway. Only down side to them is there is a limit to just how much data you can fit into a value list (at least loading them they I am via an "adstring" command). In this case it doesn't matter. Most of the lists only have 1-20 entries anyway - so easy.
I am using DAO for all connections - well I was, I am using an ADO connection to create the listbox strings for the value list. But that is all working good.
Really appreciate your time and effort on this.
cheers
Darryl.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, 9 December 2011 3:50 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com