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

[DaBears]

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.

 

[Daryl S]

DaBears -

This will cound all the objects in the MSysObjects table. Obviously, don't
mess with the system tables...

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

 

[Dirk Goldgar]

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.

Do you want to know the total number of objects in the database, or the
total number that are open at the moment? I don't 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.

 

[DaBears]

Thanks, I'll 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

 

[Marshall Barton]

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.

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
?

 

[Dirk Goldgar]

Thanks, I'll 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?

I don't think controls count at all, so far as this limit is concerned.

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

I believe all the objects are stored in the system table MSysObjects, with
some additional information stored in other system tables. However, I don't
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's not the
limiting factor.

 

[Jack Leach]

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

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Dirk Goldgar]

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

Yes.

 

[Jeff Boyce]

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're working on to a single topic, you'd never need to have
more than one form open at a time.

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

If you'd 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.