[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Posted on 2010-08-13
Medium Priority
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 asp.net, but I do still have some clients using the classic asp pages.  I've noticed that classic asp and asp.net 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 asp.net 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 58
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 200 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 asp.net 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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 58
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 asp.net 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 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1800 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

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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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