'Too many open tables?'
MoggWarroo
help under 'Table specifications' (search the Help Index for 'specifications'
as a first move) does state as the maximum number of tables which may be open -
"1,024. The actual number may be less because of tables open internally by
Microsoft Access."
I'm wondering what causes Access to open tables internally, and how one may
know the number of such open tables. My **impression** is that each combo box
on an open form which uses a SQL statement as its RowSource property may
generate an open table - while this seems in many ways unlikely, I find that if
I hit the 'Too many open tables' error, and then close a form with a given
number of combos, then I can proceed to open further forms until about the
equivalent number of new combos has been exposed!
I'd be grateful for any illumination anyone can offer!
Thanks,
Richard
Tom van Stiphout
If the combobox gets its data from a table or query, of course this is
going to count towards the number of open tables.
<clip>
My **impression** is that each combo box
>on an open form which uses a SQL statement as its RowSource property may
>generate an open table - while this seems in many ways unlikely,
<clip>
>
>Richard
Phillip Ushijima
that are opened by SQL in a combo box. It also includes any recordsets that
are opened in code as well as open forms, reports, tables and queries. In
most cases, recordsets opened by code are the cause of this error as some
Access programmers do not explicity close a recordset after it has been
opened. This situation can also be caused by forms that use an extremely
large number of combo boxes and list boxes, i.e. a configurator screen of
some sort. Check your code for recordsets that aren't explicity closed
and/or reduce the complexity of your forms.
FYI, MS Jet 3.51 ups the amount of allowed open tables to 2,048. This,
however, is not a complete solution to the problem, just a workaround.
MoggWarroo wrote in message <19990225032034...@ng05.aol.com>...
>My Access97 mdb is throwing up the message "Too many open tables". The
on-line
>help under 'Table specifications' (search the Help Index for
'specifications'
>as a first move) does state as the maximum number of tables which may be
open -
>
>"1,024. The actual number may be less because of tables open internally by
>Microsoft Access."
>
>I'm wondering what causes Access to open tables internally, and how one may
>know the number of such open tables. My **impression** is that each combo
box
>on an open form which uses a SQL statement as its RowSource property may
AJ
MoggWarroo wrote in message <19990225032034...@ng05.aol.com>...
>My Access97 mdb is throwing up the message "Too many open tables". The
on-line
>help under 'Table specifications' (search the Help Index for
'specifications'
>as a first move) does state as the maximum number of tables which may be
open -
>
etc), be careful when using recordsets in code. If you don't close them
when done, they stay in the open table list.
Michael Kaplan
both can open many tables internally for what looks like one table to you.
Michael
Phillip Ushijima <philu.n....@mcs.net> wrote in message
news:7b413f$1ph$1...@Nntp1.mcs.net...
> Any recordset will be counted as an open table, this does include
recordsets
> that are opened by SQL in a combo box. It also includes any recordsets
that
> are opened in code as well as open forms, reports, tables and queries. In
> most cases, recordsets opened by code are the cause of this error as some
> Access programmers do not explicity close a recordset after it has been
> opened. This situation can also be caused by forms that use an extremely
> large number of combo boxes and list boxes, i.e. a configurator screen of
> some sort. Check your code for recordsets that aren't explicity closed
> and/or reduce the complexity of your forms.
>
> FYI, MS Jet 3.51 ups the amount of allowed open tables to 2,048. This,
> however, is not a complete solution to the problem, just a workaround.
>
>
> MoggWarroo wrote in message
<19990225032034...@ng05.aol.com>...
> >My Access97 mdb is throwing up the message "Too many open tables". The
> on-line
> >help under 'Table specifications' (search the Help Index for
> 'specifications'
> >as a first move) does state as the maximum number of tables which may be
> open -
> >
Michael Kaplan
Michael
AJ <allen.j...@nospam.eds.com> wrote in message
news:7b406n$g3q$1...@news.ses.cio.eds.com...
>
> MoggWarroo wrote in message
<19990225032034...@ng05.aol.com>...
> >My Access97 mdb is throwing up the message "Too many open tables". The
> on-line
> >help under 'Table specifications' (search the Help Index for
> 'specifications'
> >as a first move) does state as the maximum number of tables which may be
> open -
> >
Keri Hardwick
and get a paper with much information on this topic.
Keri Hardwick
MoggWarroo wrote in message <19990225032034...@ng05.aol.com>...
>My Access97 mdb is throwing up the message "Too many open tables". The
on-line
>help under 'Table specifications' (search the Help Index for
'specifications'
>as a first move) does state as the maximum number of tables which may be
open -
>
Allen Browne
to the number of tables open.
I struck this problem in one rostering application where myriads of
combos were needed for swapping people from one timeslot to another.
Since they all had the same RowSource, I solved the problem by feed-
ing all the combos from a function in the RowSourceType property.
Since the function held the data in a static array, the combos used
zero tables once the array was initialized.
MoggWarroo
might take the form of a SQL statement, of course) causes Access (well, Jet) to
maintain one or more tables in the open state (strictly, to use-up a table
handle ID). I don't know why my pre-posting search of the Microsoft Knowledge
Base missed
Article ID: Q165272, ACC: Error Message "Can't open any more tables"
but it did! Here (in << >> ) are some key clips from the article:
All of the following add to the number of open recordsets:
<<tables, queries, bound forms and reports, combo boxes and list boxes whose
RowSource is a table or query, Data Access Objects (DAO) recordsets in code >>
Obtaining an update to Jet can help because:
<<The Microsoft Jet database engine versions 3.0 and 3.5 allow a maximum of
1024 open TableIDs at one time. Microsoft Jet version 3.51 allows a maximum of
2048 open TableIDs at one time. A TableID is a recordset pointer that the
database engine uses, and which is not exposed to the user.>>
Jet Vn3.51 is downloadable from
http://support.microsoft.com/support/downloads/LNP126.asp
I've got it - and it cures my problem!
Thanks to everyone who has contributed a response (or given the matter some
thought, but ended up stuck, like me!)
Richard
PS In the unlikely event that you don't know abut the Microsoft Knowledge Base
etc, try going to:
It will often be most fruitful to choose "Support and the Knowledge Base" under
Option 3, Choose your search category
David W. Fenton
: Phillip Ushijima <philu.n....@mcs.net> wrote in message
: news:7b413f$1ph$1...@Nntp1.mcs.net...
: > Any recordset will be counted as an open table, this does include
: recordsets
: > that are opened by SQL in a combo box. It also includes any recordsets
: that
: > are opened in code as well as open forms, reports, tables and queries. In
: > most cases, recordsets opened by code are the cause of this error as some
: > Access programmers do not explicity close a recordset after it has been
: > opened. This situation can also be caused by forms that use an extremely
: > large number of combo boxes and list boxes, i.e. a configurator screen of
: > some sort. Check your code for recordsets that aren't explicity closed
: > and/or reduce the complexity of your forms.
: >
: > FYI, MS Jet 3.51 ups the amount of allowed open tables to 2,048. This,
: > however, is not a complete solution to the problem, just a workaround.
: It is not just a mapping of one recordset to one table.... Access and jet
: both can open many tables internally for what looks like one table to you.
My impression is that any single SQL statement uses a table handle, and
every table in the SQL statement uses a handle. So does every recordsource.
Also, keep in mind that all of the Access system tables have to be open,
and when you use replication, you add about 20 or so system tables. I've
had apps that ran fine until the back end was made replicable. In all
cases, the Jet 3.51 update fixed the problem (2,048 instead of 1,024
tableIDs). It may very well be that each SQL string, table, etc., also
keeps open a reference to the relevant system tables.
Even so, I still think Access is incredibly profligate in its use of
TableIDs.
That said, hitting this error generally is a signal that you've just got
way too many datasets open simultaneously. In all cases, I've found that
reducing the number of open datasets got rid of the table handles problem,
but, more importantly, improved performance.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc