Re: "can't open any more tables"

From: james (anonymous_at_discussions.microsoft.com)
Date: 06/17/04 

Date: Thu, 17 Jun 2004 09:54:26 -0700

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
>
>--
>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: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"
    ... In a perfect world you would be right: all objects would dereference ... use them in a loop or in a query, ... Assign the workspace variable, then the transaction, ... If you do need to close a recordset, ...
    (microsoft.public.access.modulesdaovba)
  • "cant open any more tables"
    ... In my application there is a loop that does database ... updates every time through the loop. ... updates inside one transaction, so I modified the code so ...
    (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: DAO MUCH faster than ADO in this test
    ... DAO is well-known to be faster than ADO. ... Of course the DAO loop ran faster than the SQL loop; ... advantage of a table-type recordset, which only works on local tables. ... Dim starttime As Single, finishtime As Single ...
    (microsoft.public.access.modulesdaovba)
  • Re: Connection types and speeds
    ... The memory used by holding the connection open is not ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)