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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

839 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