I am about to re-start a project originallly written in AK2 and now to be
written in Access 2010. The original project was abandoned partrially because
I kept ketting error 3048 - Can't open any more databases. Much of the
problem was caused by a form having half a dozen tab controls, all with
subforms and the subforms had combo boxes on them - so lots of recordsets
open, but reaging various postings from the NG, that can be improved by only
loading the recordsets relevant to the "Active" tab control. How much that
will slow the operation, I don't know. I believe I had done all the right
things such as not using domain functions (Elookup instead), closing
recordsets and setting them to nothing etc. I understand the problem is that
there is a limit of 2048 table IDs. I understand you use 1 table ID for each
table or query that is open. How can I find out dynamically (what function is
there) to show the number of table IDs used? If I use Set MyDb = CurrentDb in
a function, do I still need to set MyDb = Nothing, or does it get released
automatically? Am I correct in assuming I would be better off not using
nested queries, but as far as possible creating all queries from tables? Does
SQL Server or something similar have the same limitations? Many thanks for
any advice, Phil

Phil wrote:
Don't know if this of any use.
http://www.youtube.com/watch?v=ovcxmeyrILQ

On 08/01/2011 15:38:35, Salad wrote:
Not really thanks

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:ig9ei5$3mj$1 (AT) speranza (DOT) aioe.org:

It's more complicated than that, actually.

You can't.

Of course you do, unless you trust VBA's reference counting to
properly release memory when variables go out of scope.

I would say so. The first time I encountered this was in A97, before
the table handles limit had been doubled to 2048, and I'd made lots
of use of nested queries. In revising the app to get round the
problem, I had to eliminate as many layers of nested queries as
possible. Indeed, the result was more efficient (even though it was,
theoretically speaking, identical in what it did), and ran faster.

I think this is a Jet limitation, and since you'd be using SQL
Server via ODBC, it wouldn't change that at all. It's only if you
were using ADO or an ADP that Jet's table handles limitations would
be irrelevant.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Salad <salad (AT) oilandvinegar (DOT) com> wrote in
news:G_6dnRFIGpGdGLXQnZ2dnUVZ_v-dnZ2d (AT) earthlink (DOT) com:

Why would it be?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

David-W-Fenton wrote:

forms/subforms in tabs. Breaking it up might be better. The OP was
focusing on his queries, not the form that might be the real culprit.

Sometimes I take the approach, when answering posts, of throwing mud at
the wall. Sometimes it sticks, other times it plops to the ground. If
that's the case, and nobody else bothered to come up with a solution, I
find no reason to get pissy about it.

On 09/01/2011 22:51:10, "David-W-Fenton" wrote:
Thanks David & Salad for your input. Looks as if I will be re-writing a lot
of queries. I note your remark that you can't find out how many Table IDs are
being used, but somehow Access must be counting them to give the error 3048
when the limit is reached. Will splitting the project into library references
help? For example I have a menu (switchboard) open the whole time to which I
set a VBA reference. The tables for this menu are held in the main database
however, not the Menu Db.

Thanks

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:igemvf$q5u$1 (AT) speranza (DOT) aioe.org:

There are lots of things that Access keeps track of that aren't
exposed to programmatic snooping, so that's not really a surprise.
In this case, it's part of Jet/ACE, not Access, and that it's not
exposed seems to me to likely be an artifact of the early design of
Access/Jet, when 1024 was surely plenty of table handles.

I think the only thing that would help would be using independent
workspaces. I'm not sure, but my guess is that the table handles
limitation is per workspace. But that's just a gut feeling on my
part. I don't see how library references would have any effect
whatsoever.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Salad <salad (AT) oilandvinegar (DOT) com> wrote in
news:84Gdncy0nrAh27fQnZ2dnUVZ_iydnZ2d (AT) earthlink (DOT) com:

I don't really think it's a matter of the structure. Whether it's
tabs or not doesn't matter -- it's how many subforms you have loaded
when the form opens. You could get rid of the tabs and use a single
subform control and swap out specific subforms, or you could use
tabs that load/unload their subforms in the OnChange event. Either
way, you've reduced the number of subforms loaded simultaneously
and, thus, the number of table handles in use.

The principle behind all efficient database application design is to
not load data until it's actually needed, and to never load more
than the user can actually use at one time. Obviously, there can be
reasons to break those rules, but one should start from there, and
if you do so, you'll have a more efficient app, and one that won't
run up against the table handles limitation. It will also upsize
quite nicely to a server back end.

I think you're misreading for tone. I really couldn't figure out
what it was you were getting at by citing the URL (I wasn't
interested in watching the entire video to try to figure out what
you meant).

In general, I think any bare citation of a URL for any purpose
without any commentary on what the URL is and the specifics of why
it is relevant to the question is a bad idea.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 11/01/2011 21:40:19, "David-W-Fenton" wrote:
Thanks, David.

Looks like trying to take the queries back to tables rather than subqueries,
and only opening subforms & combos only when needed then.

Phil