Ms Access 2010: "Cannot Open Any More Databases."

MS Access 2010: Cannot open any more databases.

I had this problem when using linked external tables. The limit was reached because about 10 excel files were used by different queries over and over again. So the number of open tables was more or less the product of queries and tables.

I imagine using unions multiplies this problem as well.

The solution for me was to copy linked excel tables into Access native tables first. Then run the very same queries with the native tables.

MS Access: Cannot open any more databases.

This may be due to a bug in an Office update, about Jan 26, 2022. See Reddit post

Try system restore to roll back the update; or...

Go into Access, File Options, Trust center and add the local front end directory and then check the box to then add the backend data location as a trusted location.

Getting Error 3048: Cannot open any more databases

So, the good news is your problem month view tab is all read-only. That will make it easier to implement my suggestions:

1. Try setting the RecordsetType to 'Snapshot' in the Form Properties for each of your subforms. This is simplest. If that doesn't work, try:

2. Use ADO disconnected recordsets. Look at this reference:

How To Create ADO Disconnected Recordsets in VBA/C++/Java

Basically, you create this disconnected recordset thingy, and set the .RecordSet propery of your subform to it:

Set mySubForm.Recordset = myDisconnectedRsObject

Since these by definition do not maintain a connection to the backend, you should be able to create as many as you like without increasing the database count.

Once you get the first one working, you will need to convert all of your Access/Jet queries underlying the 42 subforms into disconnected recordsets.

MS Access - Can't Open Any More Tables

"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.

How to get count of db recordsets/references (to debug err #3048 - can't open more databases)?

One way to do it is to just keep opening recordsets until you get an error:

Function TablesAvailable() As Integer
Dim i As Integer, rs As DAO.Recordset, rsColl As Collection

    On Error GoTo Err_TablesAvailable

    Set rsColl = New Collection
    Do While i < 4096
        i = i + 1
        Set rs = CurrentDb.OpenRecordset("SELECT 1")
        rsColl.Add rs
    Loop

Exit_TablesAvailable:
    For Each rs In rsColl
        rs.Close
        Set rs = Nothing
    Next rs
    Exit Function
Err_TablesAvailable:
    Select Case Err.Number
    Case 3048 'Cannot open any more databases.
        TablesAvailable = i
    Case Else
        MsgBox Err.Number & ": " & Err.Description
    End Select
    Resume Exit_TablesAvailable
End Function

MS Access/accdb Cannot open database error (C#)

Your connection string indicates that you are using the Microsoft.ACE.OLEDB.12.0 driver.

This must be installed on the machine, as it doesn't come with windows.

You can find the driver in the Microsoft Access Database Engine 2010 Redistributable

---

Related Topics