"can't open any more tables"

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

Date: Wed, 16 Jun 2004 17:18:26 -0700

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: 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: "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: 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)
  • Re: SqlDataReader performance
    ... so its best to get the ordinalbefore the loop begins. ... The query performes acceptable in the Query analyzer. ... As you can see most of the time is spendt getting data from the DataReader ... What effect has a transaction on this? ...
    (microsoft.public.dotnet.framework.adonet)