Dreaded "Cannot Open Any More Tables" OleDb provider .net 2.0

Posted on 2010-08-13
Last Modified: 2013-12-25
Hello Experts,

Really out of ideas on this one...  I've got a web app that hosts many different Access databases for my clients, and serves up web reports from these databases.  Everything has been working fine for quite a while, until recently (a few months ago) I started getting this error.  The unique thing about this is, once this error hits, it hits for ALL attempted connections to ANY of the Access databases.  Meaning, this wasn't specific to one database...  I can look in the application log, and all of the sudden this error will show up, and it shows up site wide no matter what database is trying to be connected to.

At first, I thought it was my code.  I thought surely I'm not closing something out properly somewhere or taking care of closing DataReaders, Command objects, or Connections somehow (all OleDb).  So I scoured my code and made sure I was closing everything properly.  Still getting the error, I changed all of my usage of command objects...  I put them all in "using" blocks to help make sure they were getting disposed of.  Still no fix.  I even went so far as to close the command object right before the 'end using' statement.  I am also disposing of the OleDbDataReader object when done.  (I should note, I am using ALL DataReader objects, and there are no update or insert queries, ONLY SELECT queries).

So then I thought it was my poor server running out of memory under load (granted, it did only have 1gb).  So this week, I just got done upgrading to a new server with 3gb of memory (moved from Win2003/IIS6 to Win2008/IIS7).  Everything has been working fine for a few days now, and then bam, here comes the error again.  This error will continue to occur until I recycle processes for the app pool.  I have the app pool set to recycle at 4:00 AM every day.

Here is how I'm connecting to the database:

                  con = New OleDbConnection(provider & "Data Source=" & HttpContext.Current.Server.MapPath("\" & sfs.DatabasePath & sfs.DatabaseName))


         ' process page and generate report here


Open in new window

I am running many different queries to generate the report, or process input from the user to determine what they want to see.  Here is how I run a typical query:

            sql = "SELECT * FROM MC WHERE EY={0} AND EID={1} AND SEID={2} AND CID={3}"
            sql = String.Format(sql, EY, EID, SEID, CID)
            Using com As New OleDbCommand(sql, con)
                Dim r As OleDbDataReader = com.ExecuteReader
                If r.Read Then
                    MID = r("MID")
                    MID = 0
                End If
            End Using

Open in new window

Other tidbits of information that may/may not be useful:

I've upgraded much of my web app to, but I do still have some clients using the classic asp pages.  I've noticed that classic asp and both trying to connect to the same database doesn't work out so good.  While I do have some clients still using the classic pages, and others using the pages, I don't intermingle the two technologies with the same database.  I think I should be OK with this approach, but maybe Jet 4.0 doesn't like this in some way?

Can't think of anything else weird at the moment.

I need to get this figured out, and I'm running out of ideas!  Will be happy to provide more code (if required) or answer any other questions about the setup.  But I am just deflated after being convinced this was a memory issue, and after all the work of moving my app to a new server, here it comes again...
Question by:Jahelka
  • 4
  • 3
LVL 57
ID: 33432131
What provider are you using?  If ODBC, then I would turn off connection pooling.
Aside from that, JET has an internal table ID limit of 2048 and when you hit that, that's the error you get.  Increasing memory doesn't help, as this is an interal limit within JET.
There are not many options.  The only thing one typically does is:
1. Reduce the number of concurrent operations (ie. closing of recordsets right away).
2. Reduce the number of nested queries.
3. If Access is being used, make sure to minimize the number of code module open (doesn't apply here).
What thows me is the pooling aspect; that you see it site wide.  That suggets the connection to JET is being shared site wide, but I don't understand that unless it's the ODBC provider.
LVL 17

Assisted Solution

jrm213jrm213 earned 50 total points
ID: 33432310
Not sure if this will make any difference or if it is causing a problem or not, but the "using statement" automatically disposes of itself, so calling dispose on the object that is used in the using statement probably isn't needed and there is a slim chance that disposing that object inside the using statement is causing it to remain open at a lower level, or at least causing the oledb connection object to think the connection wasn't closed. If you are worried about it not getting closed by the using, go ahead and dispose it right after the End Using statement.

Shot in the dark, but sometimes they hit.


Author Comment

ID: 33432613

I'm using OleDb not ODBC.  Didn't I make that clear?  =)  J/K.  I guess my question is, does the 2048 limit apply to a single connection?  Or does it apply to the Jet Engine itself?  In other words, is it one database (or one connection) that is limited to 2048, or ALL CONNECTIONS COMBINED that are limited to 2048?  If it is the latter, could my using classic asp and at the same time be causing the problem?  Also, if it is the latter, I could definitely see that under any kind of load, I could probably very easily be exceeding this limit SITE WIDE, but I can't imagine exceeding that per database.


I actually added that com.dispose in AFTER I was having problems thinking that maybe that would force it to dispose, rather than waiting on GC.  I'll have to go check, but wouldn't com be out of scope after the End Using line?  If you think it is bad practice to explicitly dispose within the Using statement block, then I will take all of those out and try it.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 57
ID: 33432845
<< Or does it apply to the Jet Engine itself? >>
Applies to the JET engine itself, so it would be all connections combined. This is what I didn't think through throughly before; in a web situation, only the server software is touching the DB, so there is only one instance of JET running, which all connections run through.
<<If it is the latter, could my using classic asp and at the same time be causing the problem?  >>
  No.  This limit exists and happens even when using the Access product on a desktop, although in that case it's much harder to hit as Access can only open one DB at a time through the UI (you can do it though).  But if your working in code, every database you open works against the limit.
  and you be surprised what counts towards that limit.  In your case, it's a little different in that your working with straight data, but every table you open and every field (a field reference contains a table ID) counts towards that limit.

Author Comment

ID: 33433120
Jim, this is GREAT stuff.  Now knowing some of the stuff you have just told me, makes me realize my worst fear.  Time to move away from Access.  That means changing code, not only on the web server, but on the windows app that my clients use to populate the database.  Lots of work ahead...

So just to run through a few more ideas...  I assume that splitting the website up into chunks, and putting say, half the website on one AppPool, and the other half on a different AppPool does me no good, it is still one instance of Jet running?

I think there are some changes I could make to my queries to lighten the load.  You mentioned something I didn't quite fully understand...  Each FIELD that I request counts against me!?!  Does that mean that I should request ONLY the fields I need for reporting rather than using * whenever I can?  Does that make any difference in the count against me?

I picked a really good example of bad coding technique in my query I listed above.  That same query that returns a whole DataReader (even though it should never have more than one record) could be re-written as r.ExecuteScalar as I only need one value from one record.  I don't think I do that anywhere else on the page, but I can go back and check.  Will that help me out in my count?
LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 450 total points
ID: 33433380
<<So just to run through a few more ideas...  I assume that splitting the website up into chunks, and putting say, half the website on one AppPool, and the other half on a different AppPool does me no good, it is still one instance of Jet running?>>
 I'm not 100% sure on the answer to that.  I would think each application pool would have to get it's own JET instance because the pools are isolated from one another.
<<You mentioned something I didn't quite fully understand...  Each FIELD that I request counts against me!?!  Does that mean that I should request ONLY the fields I need for reporting rather than using * whenever I can?>>
  You betcha.  You should only be asking for fields that you need.  If your using * and the table has 60 fields, when the query runs (assuming only one table is in the query), that's 61 TableID's used up.  
<< Will that help me out in my count? >>
  I would think it would, but I'm guessing there as the internals of how JET processes things have never been fully documented.  I only know about the table ID limit because in JET 3.5, the limit was push up from 1024 to 2048.  It was with that they (Microsoft) spelled out what counts towards that limit.  
  But it wasn't even till latter on that everyone realized their was bit more to it then that even.  In a Access app, every code module opened also uses a table ID (Access/JET uses a table ID to fetch the code from a BLOB field in one of the system tables).
 Makes sense when you think about it, but Microsoft hadn't pointed it out at the time when they raised the limit.

Author Closing Comment

ID: 33433824
Thanks for the help guys.  I'm going to start another question to ask about possible solutions for either creating a web garden (increasing worker processes above 1) and/or somehow splitting things up into separate application pools somehow to aleviate my problems until which time I can switch to a new database back end (it's going to take a while).  I've been eyeing VistaDB for a while now.  Might be time to bite the bullet and download the demo and see how it works out for me.

Author Comment

ID: 33433956
Started up a new question to further my learning here:

Hopefully I get a good solution there.  Just wanted to share for anyone else that wanted to continue on the topic at hand.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Remove Selected Values from Access Combobox 2 33
MS Access, Class Objects, Modules, Class Modules 6 34
Calculating percentage 2 27
Access 2010 7 31
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question