DataBase - is there a way to get number of "objects" in a Access db?

Asked By DaBears

20-Nov-09 09:19 AM

I have a mdb with a large number of forms open at the same time.  I think I
might be hitting the 32768 object limit.  Is there any way to monitor the
object count so I can prove or disprove my suspicions.

20-Nov-09 10:07 AM

DaBears -

This will cound all the objects in the MSysObjects table.  Obviously, do not
mess with the system tables...

SELECT Count(MSysObjects.Id) AS CountOfId
FROM MSysObjects;

--
Daryl S

20-Nov-09 10:24 AM

Do you want to know the total number of objects in the database, or the
total number that are open at the moment?  I do not know a summary way to get
the count of all objects, but you can get the number of objects of each
type.

Forms -
Total count = CurrentProject.AllForms.Count
Open count = Forms.Count

Reports -
Total count = CurrentProject.AllReports.Count
Open count = Reports.Count

Modules -
Total count = CurrentProject.AllModules.Count
(Doesn't include form/report modules)

Open count = Modules.Count
(includes any form/report modules that are open
in the VB Editor)

Macros -
Total count = CurrentProject.AllMacros.Count
Open count  -- code is required to determine this:

Dim NOpenMacros As Long
Dim ao As AccessObject
For each ao In CurrentProject.AllMacros
If ao.IsLoaded Then NOpenMacros = NOpenMacros + 1
Next ao
' NOpenMacros now contains the number of open macros.

Tables -
Total count = CurrentData.AllTables.Count
Open count  -- code is required to determine this:

Dim NOpenTables As Long
Dim ao As AccessObject
For each ao In CurrentData.AllTables
If ao.IsLoaded Then NOpenTables = NOpenTables + 1
Next ao
' NOpenTables now contains the number of open tables.

Queries -
Total count (user queries) = CurrentData.AllQueries.Count
Total count (user & system queries) = CurrentDb.QueryDefs.Count
Open count  -- code is required to determine this:

Dim NOpenQueries As Long
Dim ao As AccessObject
For each ao In CurrentData.AllQueries
If ao.IsLoaded Then NOpenQueries = NOpenQueries + 1
Next ao
' NOpenQueries now contains the number of open Queries.


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

20-Nov-09 10:44 AM

Thanks, I will take a look at this.  Wouldn't controls on forms and reports
also contribute to object count?  Do they count only if form/report is open?

My thinking is that if Access as limit of 32,768 objects, then how does it
keep track?  Simple table

20-Nov-09 10:47 AM

That is really difficult for me to imagine.  Note that the
the number of objects in the database is not the same as the
number of open forms/reports, etc.

You can get the numbe of open forms by using ?Forms.Count in
the Immediate window

I would also be concerned that you might have some code that
opesn recordsets in a loop, creates querydefs on the fly, or
?

--
Marsh
MVP [MS Access]

20-Nov-09 11:01 AM

I do not think controls count at all, so far as this limit is concerned.


I believe all the objects are stored in the system table MSysObjects, with
some additional information stored in other system tables.  However, I do not
know the details of this structure nor why the limit is so small.  The
primary key field of MSysObjects is a long integer, so that is not the
limiting factor.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

20-Nov-09 11:11 AM

This limit is for the total number of objects, closed or open, correct?

--
Jack Leach
www.tristatemachine.com

-Thomas Edison (1847-1931)

20-Nov-09 11:51 AM

Define "large" ... (do you mean more than 3, more than 10, more than 100?)

If more than 3, why?  Do you expect your users to be simultaneously using
multiple screens, performing multiple tasks?  (are they all under 30?<g>).

As an alternate approach, if you provide your users a single form at a time,
focusing what they are working on to a single topic, you would  never need to have
more than one form open at a time.

Of course, I do not have any idea of your specific situation because you
have not described it, so the above ideas may not be germane.

If you would  like folks here to offer more specific suggestions, provide more
specific descriptions.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

20-Nov-09 11:49 AM

Yes.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)