“Unable to open more tables” is a better error message than “Unable to open more tables”, which is more common in my experience. In fact, the last message almost always disguises the first.
The Jet 4 database engine has a limit of 2048 table descriptors. It is not entirely clear to me whether this is simultaneous or cumulative during the life of the compound. I always thought that it was cumulative, since opening fewer records in practice in practice seems to avoid the problem.
The problem is that "table descriptors" refer not only to table descriptors, but also to something much larger.
Consider a saved QueryDef with this SQL:
SELECT tblInventory.* From tblInventory;
Running this QueryDef uses TWO descriptors.
What ?, you ask? It uses only one table! But Jet uses a table descriptor for the table and a table descriptor for the stored 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 original queries contains two tables, you use these table descriptors, 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 that you have only four tables (because there are only four base tables), but you will actually use 7 table descriptors to use these 4 base tables.
If you then use a stored QueryDef in the recordset, which uses 7 table descriptors, you used another table descriptor for a total of 8.
Back in Jet 3.5 days, the original table restricts the limit to 1024, and I ran into it as soon as I was able to replicate the data file after developing a working application. The problem was that some replication tables were always open (perhaps for each set of records?), And for this, just enough table descriptors were used to transfer the application over.
In the original design of this application, I opened a bunch of heavyweight forms with a lot of subforms and combo boxes and lists, and at that time I used a lot of saved QueryDefs to pre-build standard record sets that I would use in many places (just like viewing any server database). Bug fixed:
Download subforms only when they have been shown.
loading lines in lists from lists and lists only when they were on the screen.
Get rid of all saved QueryDefs and use the SQL statements that join the source tables whenever possible.
This allowed me to deploy this application in the London office only a week later than planned. When Jet SP2 came out, it doubled the number of table descriptors that we still have in Jet 4 (and, I suppose, ACE).
In terms of using Jet from Java through ODBC, the key point is, I think:
use one connection throughout the application, instead of opening and closing them as needed (which does not give you the opportunity to close them).
open records only when you need them, and clean and free your resources when you are done.
It may now be that there are memory leaks in the JDBC => ODBC => Jet chain where you think you are releasing resources and they are not being released at all. I don't have any JDBC-specific tips (since 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 freeing their memory structures, because VBA uses reference counting, and sometimes it doesn’t know that the reference to the object was released, so it does not free memory for this object when it goes out of scope.
So, in VBA code, when 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 have done what you need to do, you need to end 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 even if your declared variables go beyond immediately after this code (which should free up all the memory they use, but not 100% reliable).
Now I'm not saying that this is what you do in Java, but I just suggest that you have problems, and you think that you are releasing all your resources, maybe you need to determine re depending on the garbage collection, so that do it, and instead you need to do it explicitly.
Forgive me if I said something stupid regarding Java and JDBC - I just report some problems that Access developers had when interacting with Jet (through the DAO, not ODBC) that report this error message, which you get, in the hope that our experience and practice can offer a solution for your specific programming environment.