MS Access 2010: "Cannot open any more databases."

Question

While struggling with a single legacy MS Access application I faced this weird error:

Cannot open any more databases.

The application makes extensive use of UNION sql statements. So this seems to cause access hitting the limit of 2048 open tables. Any other chance than getting rid of these unions?

Answer 1

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.

Answer 2

Often, this occurs with big/complex forms with many subforms and/or comboboxes/listboxes.

Try to do what Saurabh says. Are good things anyway. But i think that these changes will not solve your problem.

Recently, i solve the same problem. I identified that always occurs when a given form were opened. This form had many subforms and combos.

First. Try to make your form or forms simpler: do you really need all subforms? All subforms must be loaded always?

I solve my problem distributing subforms in diferent pages of a tab control. Then load and unload subforms dynamically in Change event.

Initially, only subforms on the first page must have the "SourceObject" property assigned. The rest, has this property empty.

In change event, try to do something like this:

Private Sub TabControl_Change
    Dim pgn As Access.Page
...
    For Each varCtlSubform In Array(Me.Subform1, Me.Subform1, ...)
        Set pgn = varCtlSubform.Parent
        If pgn.PageIndex <> Me.TabControl.value Then
            if varCtlSubform.SourceObject <> "" Then
                varCtlSubform.SourceObject = ""
            End if
        Else
            If varCtlSubform.SourceObject <> ctlSubform.Tag then
                varCtlSubform.SourceObject = ctlSubform.Tag
            End if
        End If
    Next
...
End sub

This is a generic function to iterate on all subform controls. If isn't in the active page, unload it. In other case, take source object from tag property.

You'll need to avoid references to unloaded subforms, i.e., if "Subform1" is unloaded you'll get an error with anything like this: Me.Subform1.Form.InvoiceId

This change have other benefits. Your form will load faster and record navigation will be faster.