MS Access - Can't Open Any More Tables

Question

at work we have to deal with several MS Access mdb files, so we use the default JdbcOdbcBridge Driver which comes with the Sun JVM and, for most cases, it works great.

The problem is that when we have to deal with some larger files, we face several times exceptions with the message "Can't open any more tables". How can we avoid that?

We already close all our instances of PreparedStatements and RecordSets, and even set their variables to null, but even so this exception continues to happen. What should we do? How can we avoid these nasty exceptions? Does someone here knows how?

Is there any additional configuration to the ODBC drivers on Windows that we can change to avoid this problem?

Solution 1

"Can't open any more tables" is a better error message than the "Can't open any more databases," which is more commonly encountered in my experience. In fact, that latter message is almost always masking the former.

The Jet 4 database engine has a limit of 2048 table handles. It's not entirely clear to me whether this is simultaneous or cumulative within the life of a connection. I've always assumed it is cumulative, since opening fewer recordsets at a time in practice seems to make it possible to avoid the problem.

The issue is that "table handles" doesn't just refer to table handles, but to something much more.

Consider a saved QueryDef with this SQL:

  SELECT tblInventory.* From tblInventory;

Running that QueryDef uses TWO table handles.

What?, you might ask? It only uses one table! But Jet uses a table handle for the table and a table handle for the saved QueryDef.

Thus, if you have a QueryDef like this:

  SELECT qryInventory.InventoryID, qryAuthor.AuthorName
  FROM qryInventory JOIN qryAuthor ON qryInventory.AuthorID = qryAuthor.AuthorID

...if each of your source queries has two tables in it, you're using these table handles, one for each:

  Table 1 in qryInventory
  Table 2 in qryInventory
  qryInventory
  Table 1 in qryAuthor
  Table 2 in qryAuthor
  qryAuthor
  the top-level QueryDef

So, you might think you have only four tables involved (because there are only four base tables), but you'll actually be using 7 table handles in order to use those 4 base tables.

If in a recordset, you then use the saved QueryDef that uses 7 table handles, you've used up yet another table handle, for a total of 8.

Back in the Jet 3.5 days, the original table handles limitation was 1024, and I bumped up against it on a deadline when I replicated the data file after designing a working app. The problem was that some of the replication tables are open at all times (perhaps for each recordset?), and that used up just enough more table handles to put the app over the top.

In the original design of that app, I was opening a bunch of heavyweight forms with lots of subforms and combo boxes and listboxes, and at that time I used a lot of saved QueryDefs to preassemble standard recordsets that I'd use in many places (just like you would with views on any server database). What fixed the problem was:

1. loading the subforms only when they were displayed.

2. loading the rowsources of the combo boxes and listboxes only when they were onscreen.

3. getting rid of all the saved QueryDefs and using SQL statements that joined the raw tables, wherever possible.

This allowed me to deploy that app in the London office only one week later than planned. When Jet SP2 came out, it doubled the number of table handles, which is what we still have in Jet 4 (and, I presume, the ACE).

In terms of using Jet from Java via ODBC, the key point would be, I think:

1. use a single connection throughout your app, rather than opening and closing them as needed (which leaves you in danger of failing to close them).

2. open recordsets only when you need them, and clean up and release their resources when you are done.

Now, it could be that there are memory leaks somewhere in the JDBC=>ODBC=>Jet chain where you think you are releasing resources and they aren't getting released at all. I don't have any advice specific to JDBC (as I don't use it -- I'm an Access programmer, after all), but in VBA we have to be careful about explicitly closing our objects and releasing their memory structures because VBA uses reference counting, and sometimes it doesn't know that a reference to an object has been released, so it doesn't release the memory for that object when it goes out of scope.

So, in VBA code, any time you do this:

  Dim db As DAO.Database
  Dim rs As DAO.Recordset

  Set db = DBEngine(0).OpenDatabase("[database path/name]")
  Set rs = db.OpenRecordset("[SQL String]")

...after you've done what you need to do, you have to finish with this:

  rs.Close         ' closes the recordset
  Set rs = Nothing ' clears the pointer to the memory formerly used by it
  db.Close
  Set db = Nothing

...and that's even if your declared variables go out of scope immediately after that code (which should release all the memory used by them, but doesn't do so 100% reliably).

Now, I'm not saying this is what you do in Java, but I'm simply suggesting that if you're having problems and you think you're releasing all your resources, perhaps you need to determine if you're depending on garbage collection to do so and instead need to do so explicitly.

Forgive me if I'd said anything that's stupid in regard to Java and JDBC -- I'm just reporting some of the problems that Access developers have had in interacting with Jet (via DAO, not ODBC) that report the same error message that you're getting, in the hope that our experience and practice might suggest a solution for your particular programming environment.

Solution 2

Recently I tried UCanAccess - a pure java JDBC Driver for MS Access. Check out: http://sourceforge.net/projects/ucanaccess/ - works on Linux too ;-) For loading the required libraries, some time is needed. I have not tested it for more than read-only purposes yet.

Anyway, I experienced problems as described above with the sun.jdbc.odbc.JdbcOdbcDriver. After adding close() statements following creation of statement objects (and calls to executeUpdate on those) as well as System.gc() statements, the error messages stopped ;-)

Solution 3

There's an outside chance that you're simply running out of free network connections. We had this problem on a busy system at work.

Something to note is that network connections, though closed, may not release the socket until garbage collection time. You could check this with NETSTAT /A /N /P TCP. If you have a lot of connections in the TIME_WAIT state, you could try forcing a garbage collection on connection closes or perhaps regular intervals.

Comments

[Kico Lobo]

I agree that the jdbcodbc driver is far from good. We face many many many problems with it daily. Unfortunetely we have some budget restraints that doesn´t allow us to buy any other alternative driver :( And actually we close all the connections. What pisses me off about it is that in this case we are doing everything exactly like all the books (we hope the goods :)) say!

[Kico Lobo]

Great tip! I'll check this out. Actually, I always set my instances of Connection, PreparedStatement and ResultSet while working with Access to null exactly because the JdbcOdbcDriver doesn't close them if they are not null. I checked this out with some experiments in our company. But what amazes me is the fact that untill now NO patch was made for this issue.

[Kico Lobo]

David, this is an AWESOME explanation about this issue! Thanks you very much!

[Kico Lobo]

Actually, does Jet4 works with Access 97? Is it possible to configure the number of database handles I can use with Jet? If so, how should I do that?

[David-W-Fenton]

A97 uses only Jet 3.5, but as long as you have the last service release of Jet 3.5, it has the same limit as Jet 4.0. This is not user configurable -- it's a hard top limit.