Re: "can't open any more tables"

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 10:16:51 +0800

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 

-- 
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.
"james" <anonymous@discussions.microsoft.com> wrote in message
news:1dd8401c4548b$bf6c2720$a101280a@phx.gbl...
> 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
> >-----Original Message-----
> >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" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:1db3001c45400$9bd02860$a001280a@phx.gbl...
> >> 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

Relevant Pages

  • Re: "cant open any more tables"
    ... Assign the workspace variable, then the transaction, ... If you do need to close a recordset, ... >> updates every time through the loop. ...
    (microsoft.public.access.modulesdaovba)
  • Re: "cant open any more tables"
    ... by settting the Recordset variable to Nothing. ... Avoid domain aggregate functions such as DLookupinside the transaction ... > updates every time through the loop. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... You may be right about the transaction. ... round trip per each loop. ... with one database update trip PER RECORD is catastrophic. ... paging issues to keep the memory resources under control if you need better ...
    (microsoft.public.data.ado)
  • Re: Transactions - Infinite Loops
    ... This still doesn't sound like an infinite loop. ... > It was an unusual SQL Server response that we were unable to duplicate. ... > transaction if the wrong number of felds are being updated (i.e., ... > the query ran okay. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transactions - Infinite Loops
    ... If you're really in a loop, you will not be able to enter any more commands. ... If you just forgot to commit, SQL Server cannot tell you that, as it has no ... of the same transaction. ... COMMIT TRAN. ...
    (microsoft.public.sqlserver.programming)