Too Many Open Databases

I am running a large Access DB (Access Backend and Access Front end using
terminal services)

Am getting a lot of errors on "Too many dataases Open" in the system.

I am trying to work out if there is a system setting that controls how many
databases are allowed to be open in Access ?

(Normally get this after openning more then 4 to 6 active windows (With sub
forms))

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ian Footrot" Ian wrote in message
...
I am running a large Access DB (Access Backend and Access Front end using
terminal services)

Am getting a lot of errors on "Too many dataases Open" in the system.

I am trying to work out if there is a system setting that controls how
many
databases are allowed to be open in Access ?

(Normally get this after openning more then 4 to 6 active windows (With
sub
forms))