P
Peter Whyte
I've been having an intermittent problem with a db I'm developing.
Hoping someone can shed some light on it. (I've read similar queries
on these groups before, but nothing seems to match my circumstances,
the systems seems even more complex than mine, and I didn't think there
was any conclusions reached.)
The db has b/e tables, a main client form that has 3 different tab
controls. One tab control has a read only subform, and 2 other tabs
from the main form, while the other two controls display listboxes
whose record sources are changed programmatically when the pages are
changed. Everything is bound. Altogether there are 2 listboxes and 7
combos. Most of these are short lists. Most of the queries on which
they are based are simple, involving a single table. Occasionally there
is a complex one (eg doctors which uses about 6 tables/queries).
Each listbox opens a form with further details of the list. A couple of
them can go deeper, which is where I seem to have the problem. The
client doctor form has a combo to select a doctor. A cmd will open a
further form with a listbox listing all the doctors, from which a new
doctor can be added via a new form. The town for the doctor is a combo
which in turn will open a list of towns from which a new town record
can be entered. And finally the county of the town does the same. Each
form opened in the chain is modal. Each combo is managed by an attached
class object which manages some events automatically, and each form
also has a FormInfo class associated with it that has a reference to
the form itself. There's a global stack of forms to make sure that the
forms are closed in reverse order. (I use the same form for each list,
and just instantiate a new copy each time with args passed in to set it
up for each list.)
I can usually open the full chain of forms and return successfully, but
on occasion when I hit town or county I get error 3048 cannot open any
more databases. I have discovered from other postings elsewhere that
this is probably something to do with Table IDs, which are limited in
Access 2003 to 2048. I've been careful to close any recordsets I've
opened, and whereever I need to refer to the current db I've avoided
using CurrentDb() as this increases the database count. Instead I've
used DBEngine(0)(0) which does not open additional databases - I've
checked the count in debugging statements and verified this for
certain.
I can't seem to figure out why I'm getting the error, or how I can
avoid it. At most there would be 9 forms open in a chain. Taking
complex recordsets for each one, say of 5-6 tables/queries, and
allowing for 5-6 tables/queries per list/combobox, and doubling the
handles for using a backend database with linked tables, I can't see me
hitting 2048, even if Access itself is using a couple of dozen for its
own use.
Sorry for the lengthy posting - I just didn't want to be told close
your recordsets, etc., as I've done all that, and taken great steps to
avoid opening things unnecessarily. Any help would be greatly
appreciated, as I'm at the end of my tether with this after several
weeks of hitting my head against a brick wall.
Hoping someone can shed some light on it. (I've read similar queries
on these groups before, but nothing seems to match my circumstances,
the systems seems even more complex than mine, and I didn't think there
was any conclusions reached.)
The db has b/e tables, a main client form that has 3 different tab
controls. One tab control has a read only subform, and 2 other tabs
from the main form, while the other two controls display listboxes
whose record sources are changed programmatically when the pages are
changed. Everything is bound. Altogether there are 2 listboxes and 7
combos. Most of these are short lists. Most of the queries on which
they are based are simple, involving a single table. Occasionally there
is a complex one (eg doctors which uses about 6 tables/queries).
Each listbox opens a form with further details of the list. A couple of
them can go deeper, which is where I seem to have the problem. The
client doctor form has a combo to select a doctor. A cmd will open a
further form with a listbox listing all the doctors, from which a new
doctor can be added via a new form. The town for the doctor is a combo
which in turn will open a list of towns from which a new town record
can be entered. And finally the county of the town does the same. Each
form opened in the chain is modal. Each combo is managed by an attached
class object which manages some events automatically, and each form
also has a FormInfo class associated with it that has a reference to
the form itself. There's a global stack of forms to make sure that the
forms are closed in reverse order. (I use the same form for each list,
and just instantiate a new copy each time with args passed in to set it
up for each list.)
I can usually open the full chain of forms and return successfully, but
on occasion when I hit town or county I get error 3048 cannot open any
more databases. I have discovered from other postings elsewhere that
this is probably something to do with Table IDs, which are limited in
Access 2003 to 2048. I've been careful to close any recordsets I've
opened, and whereever I need to refer to the current db I've avoided
using CurrentDb() as this increases the database count. Instead I've
used DBEngine(0)(0) which does not open additional databases - I've
checked the count in debugging statements and verified this for
certain.
I can't seem to figure out why I'm getting the error, or how I can
avoid it. At most there would be 9 forms open in a chain. Taking
complex recordsets for each one, say of 5-6 tables/queries, and
allowing for 5-6 tables/queries per list/combobox, and doubling the
handles for using a backend database with linked tables, I can't see me
hitting 2048, even if Access itself is using a couple of dozen for its
own use.
Sorry for the lengthy posting - I just didn't want to be told close
your recordsets, etc., as I've done all that, and taken great steps to
avoid opening things unnecessarily. Any help would be greatly
appreciated, as I'm at the end of my tether with this after several
weeks of hitting my head against a brick wall.