Darryl Collins
darryl at whittleconsulting.com.au
Thu Dec 8 17:45:35 CST 2011
Well, Yes and no. If you are at the absolute limit because you have > 754 controls then you are screwed - and moving to a new form won't help at all.
On an existing form that is corrupt or crashing though, there is hope. Access forms are a bit stupid it seems when it comes to counting controls. If you have a single form and add 753 controls, Save the form. Then you delete the controls and add another 5. Then the form will crash even though there are only 5 controls on the form. Trouble is, the control count is absolute and doesn't decrease when you delete controls - everytime you add a control it adds 1 to the count regardless of if the control still exists or not.
This is highly bothersome if you consistently copy a default type form and then remove and add new controls. Sooner or later you hit the limit regardless of the number of controls on the form.
In this instance importing the controls into a brand new form DOES help, as the control count starts a zero on a new form.
Many times, on control heavy forms I have used this to get out of bother. Now why they have the limit, and why it doesn't decrease when delete controls is a question for someone deep in the MS bunker, Redmond WA USA.
Cheers
Darryl
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Friday, 9 December 2011 8:29 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Advice on A2010....
Copies of the form are full copies with the same limits. The control count is part of the properties of the form, so importing it doesn't change a thing. You can create a new form and copy the controls to it but then you have to go through replacing the old form with the new one.
Charlotte Foust
On Thu, Dec 8, 2011 at 8:57 AM, Heenan, Lambert < Lambert.Heenan at chartisinsurance.com> wrote:
> Jim,
>
> Regarding the (crazy) form control limit. Do you know if the count
> gets reset if you make a copy of the form? Does the new copy have a
> controls count equal to the number of controls currently on the form?
> What about importing the form into another MDB file?
>
> Just curious, I've not yet hit that limit.
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Thursday, December 08, 2011 11: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
>
>
>
> --
> 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