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.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month12 days, 2 hours left to enroll

752 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