"Too Many Open Tables" using ADO in VB.NET

[NatHunter]

I am migrating an application from VB6 to VB.NET. I am using ADO initially - I'd like to get the program running under ADO before upgrading to ADO.NET.

I'm nearly there - the finalish problem is that I get a "Too Many Open Tables" message when the application is running, where I didn't in VB6.

I use code to create a recordset, populate a class and then close the recordset using rs.close, so I'm not sure why there should be an ever-increasing number of tables open, as appears to be the case. Does anyone have any pointers as to why this should be the case, particularly as all worked fine under VB6 ADO...

Thanks for any help

Paul

 

[chiph]

What database are you using?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[NatHunter]

I am using ADO and MS Access. Curiously enough, the problem has appeared to gone away. Perhaps rebooting has cured something. All seems a bit strange to me..

 

[chiph]

Access does odd things.

I would suggest you change to MSDE when you get a chance.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ThatRickGuy]

I get paranoid any time an application has a "Repair" option in the menu. It makes me think that there is a known issue that will cause it to Break in the first place.

-Rick

----------------------

http://www.ringdev.com

 

[pweegar]

The "Compact and Repair" option is there to make your Access tables run more efficiently by removing wasted space and to fix or rebuild indexes among other things. I would say most, if not all databases have such facilities, even if they are called by a different name and done automatically. SQL Server can do also fix indexes, etc when you do a backup. We force this option on every backup, everyday. It's a fairly fast operation (approx 20 mutes to do a backup on a 7gig db, done off hours).

Adamsp:
If you get the error message again, instead of rebooting, try closing your app and restarting it. Does that solve the problem?

Also, in your code, if youhaven't, check the state of the table.

http://www.devguru.com

is a good site for ADO commands. You want to check the state of the connection, command and recordset objects to see if they are open or closed, etc.

 

[ThatRickGuy]

I'm very familiar with compacting an reindexing databases. My issue is not with that process, but with the specific functionality of Access. The compact and repair does more then just reindex the tables. Deleting abandoned records, reindexing, compressing, attempting to correct data corruption, lock file issues, tons of stuff. The problem is that Access is such a bloated pig to begin with. We unfortunatly still have a hand full of Access apps that haven't been converted yet. One of them, after running 'Compact and repair' on a weekly automated schedule drops from ~300 megs to ~25 megs. So the problem is not the compact and repair, but the junk that swelled the database up to 300 megs in the first place. Just imagine if a large scale application running on SQL Server or Oracle would for some unknown reason expand to 12 times it's size every week.

-Rick

----------------------

http://www.ringdev.com

 

[chrissie1]

well actually if you don't backup sql-server then the transaction logs can be more then 12times it's size a day if you have lots of updates and inserts.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ThatRickGuy]

But atleast then it is a known reason.

-Rick

----------------------

http://www.ringdev.com