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" <(E-Mail Removed)> wrote in message
news:1dd8401c4548b$bf6c2720$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
> message
> >news:1db3001c45400$9bd02860$(E-Mail Removed)...
> >> 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
Similar Threads
