"can't open any more tables"

[james]

In my application there is a loop that does database
updates every time through the loop. All the updates are
done in a transaction. If the loop executes too many
times, I get an error "can't open any more tables".

I thought that this was because there were too many
updates inside one transaction, so I modified the code so
that the transaction is committed every 500 times through
the loop. Now, when I try to run with about 1500 loop
iterations, two transactions are successfully committed,
corresponding to 1000 times through the loop. However,
subsequently the application fails with the same error
message.

What can cause this error besides too many updates inside
a transaction? I'm using Access 97.

Thanks in advance,
james

 

[Allen Browne]

Hi James.

There are several possible issues here.

1. Assign the workspace variable, then the transaction, then the database
variable.

2. Avoid opening and closing lots of recordsets if possible. It will be much
more efficient to hold them open.

3. If you do need to close a recordset, be sure to explicitly close AND
dereference, by settting the Recordset variable to Nothing.

4. Always close what you open, and dereference your variables.

5. Avoid domain aggregate functions such as DLookup() inside the transaction
if possible. They do not clean up after themselves, and do not examining the
results inside the workspace anyway.

6. Explicitly roll back after any error. Otherwise you leave uncommitted
transactions hanging.

There is a list of the traps and an example of working with transactions in
this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

 

[james]

Thanks Allen. I am wondering, is it necessary to set
recordset variables to nothing after closing them, if the
variable is about to go out of scope? It would seem that
the reference counter is going to be decremented anyway
when the variable goes out of scope, so that setting it
to nothing shouldn't be necessary.

Thanks,
Paul

 

[Allen Browne]

Hi James.

In a perfect world you would be right: all objects would dereference
themselves automatically.

Access is very good at releasing objects, but not perfect. In Access 97, two
scenarios came to light where objects would not be released at all, so there
was no way to close Access without using the Task Manager (Ctrl+Alt+Del).
Tracking down the culprits amongst many thousands of lines of code was a
real pig of a job.

So, most of us are very careful to close everything you open (and only what
you open), and dereference all objects (forms, recordsets, anything you Set)
by setting them to Nothing. We do that in the error-recovery block, so they
are still dereferenced after an error. If we reuse an object variable in a
routine, then we set it to Nothing before setting it to somethng else. The
order of dereferencing also matters if there are dependencies between the
objects.

So, it is actually one of those factors that can contribute to the error
message you received. In some versions of Access at least, the domain
aggregate functions don't clean up after themselves completely, so if you
use them in a loop or in a query (where they are called from every row),
they demonstrate how not cleaning up does generate this kind of error
(though it may have been "can't open any more databases."). That's one of
three reasons we use a replacement for DLookup(), as shown here:
http://allenbrowne.com/ser-42.html

 

[Dirk Goldgar]

[...] In some versions of Access at least, the
domain aggregate functions don't clean up after themselves
completely, so if you use them in a loop or in a query (where they
are called from every row), they demonstrate how not cleaning up does
generate this kind of error (though it may have been "can't open any
more databases.").

Allen, in what versions of Access is this the case? I hadn't heard
that, and would like to guard against it.

 

[Allen Browne]

Was long ago: 95 and 97 I think, and before the service pack that doubled
the number of open tables allowed.

Cannot comment on recent versions, as I've tried to avoid this since then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[...] In some versions of Access at least, the
domain aggregate functions don't clean up after themselves
completely, so if you use them in a loop or in a query (where they
are called from every row), they demonstrate how not cleaning up does
generate this kind of error (though it may have been "can't open any
more databases.").

Allen, in what versions of Access is this the case? I hadn't heard
that, and would like to guard against it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Was long ago: 95 and 97 I think, and before the service pack that
doubled the number of open tables allowed.

Cannot comment on recent versions, as I've tried to avoid this since
then.

Thanks, Allen. I don't think I'm likely to run into that problem, but
it's good to be forewarned.