This is a hard-wired limit. In Access 97, it was increased by one of the
service packs, and (from memory) is 2048 since then.
The worst offenders are the domain aggregate functions - DLookup(),
DCount(), DMax(), etc - when you use them in a query, so that they have to
run for every row. If possible replace them with a join, a subquery, or at
least code that cleans up after itself such as:
http://members.iinet.net.au/~allenbrowne/ser-42.html
Next offender is the combo box. If you have heaps of these open, each one
has its own RowSource, and contributes to the issue. If you have many combos
with the same rowsource (e.g. a rostering app where there are combos
everywhere for selecting a staff member for the timeslot), you may be able
to work around that by changing the RowSourceType to a callback function
that supplies the data from a static array.
After that, every subform requires its own RecordSource, so if you have
tabbed controls with a different subform on each page, you could possiby
work around that by placing a single subform directly on the main form (not
on a page of the tab control), and changing the SourceObject of the subform
in the Change event of the tab control. A side-effect of doing this is that
Access is likely to reassign the LinkMasterFields/LinkChildFields on a whim,
so you may need to set these also after changing the SourceObject.
Access also opens a separate database connection for each form where you
refer to RecordsetClone.
If that doesn't give you some good leads, you may be able to find out what's
being used by looping through the Databases collection of each of the
Workspaces of dbEngine after the software has been used for a while.
