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?
"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:
loading the subforms only when they were displayed.
loading the rowsources of the combo boxes and listboxes only when they were onscreen.
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:
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).
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.