Re: "can't open any more tables"

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

Date: Thu, 17 Jun 2004 12:24:55 +0800

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)
  • 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)
  • Re: commit or rollback a transaction without first beginning a transaction
    ... I understood the opposite; that when a recordset ... the recordset before entering the transaction, ... > what happens with a Jet recordset. ... >> a single connection, so when SQLTransact is called, all statements on the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Delete event not followed up with BeforeDelConfirm or AfterDel
    ... I take it you are trying to wrap your own transaction around all the ... inserts, edits, and deletes in both a main form and a subform. ... decision to either commit or rollback. ... recordset within that transaction, open a form, and assign your recordset to ...
    (microsoft.public.access.forms)
  • 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)
Loading