Error : Can not open any more tables

[riddhi]

hi,



We have developed one multithreaded application and we are using Microsoft Access Jet engine 4.0. And when i run the application it execute successfully but after some time it gave an error "Can not open any more table." We searched a lot and we found that we have to close all the connections and all the readers. and release all the resources. we have done that also but its not working. so can any one help us??

Regards,

Riddhi

[orange]

Please tell us more about your multithreaded application and the database.
How many tables? Picture of Tables and relationships. Split database FE/BE....

Perhaps you could show us a little of the code you use to open/close tables.

[riddhi]

hi orange,

Thanks for your reply. We have developed a mail server which is multi threaded application. It is in VB.net. And we are using MS Access database in that 33 tables are there. we are using readers to retrieve the data from the database. We are using virtual tables also. It works fine in begining but after some times it throws exception "Can not open any more table". We debugged the code also and we found that after some time when we read data from database using DataReader, it throws an exception and stops execution. We made 5 windows service. All the services are started running when we start our machine. And in all the services we are opening the database connection with different connection objects. So what should we do now??

Riddhi.

[orange]

I am not familiar with vb.net. It seems to me that your application would use various events-- button click etc. If you Open a table /recordset on the click of a button, you should do your processing, then close and release the resource. I can't think of anything that would keep tables open, other than some or several processes NOT releasing the resource when the process has completed.

Here is a quote from an Allen Browne response to a post re: setting a reference to Nothing

Allen Browne
re: Do I need to set object = nothing?

It is good programming practice to close what you opened (by only what you
opened), and to dereference your objects.

In a perfect world, this would be unnecessary. Access would reliably close
anything you opened and dereference any objects you set as soon as the
procedure terminated and the objects went out of scope. We don't live in a
perfect world. There were bugs in Access 97, such that it was impossible to
close Access if you did not explicitly close the recordsets you opened.
Access would minimize to the taskbar when you tried to close it, and the
only solution was to use the task manager (Ctrl+Alt+Del) to kill it.

Even after Microsoft fixed that bug, later versions still had problems with
running out of databases if you did not explicitly set your database
variables to Nothing if they had already been set to CurrentDb(). Haven't
tried recently, but it was easy enough to do. Just run a loop that call a
function a few hundred times, and in the function set a Database type
variable to CurrentDb.

So, is it worth the effort to explicitly close what you opened and
de-reference your objects? Up to you, but the pain we had with the A97 bugs
(other causes as well as the Recordset one), and the difficulty of tracking
and fixing those left many of us rather fastidious about cleaning up after
ourselves instead of expecting the software to do it perfectly.

Good luck.

There are code examples at (DAO)
http://allenbrowne.com/func-DAO.html
and (ADO)
http://allenbrowne.com/func-ado.html