MS Access 2010: “Cannot open any more databases.”

问题:

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?

回答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.

回答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.

回答3:

The only real way around this problem is to use a temporary set of tables. Insert the results from your unions into temp tables and then use those to limit the number of tables per query. I usually prefix my temp tables with an underscore ( _tmpCustomers ) and then destroy them when I'm done.

回答4:

I want to thank ricardohzsz for his wonderful code! It really helped me improve my database performance as well as eliminate error 3048.

I would vote the post up but I don't have enough of a reputation on here to vote.

I had to make some modifications to get it to work for my needs (I needed the subforms to allow additions and edits and using this code made them read-only). I am posting the alterations here in case it may help somebody else, too:

Private Sub TabControlMain_Change()

Dim pgn As Access.Page
Dim sbf As SubForm
Dim strSubForm As String
Dim VarCtlSubform As Variant


For Each VarCtlSubform In Array(Me.sf1, Me.sf2, Me.sf3, etc) 
Set pgn = VarCtlSubform.Parent
    If pgn.PageIndex <> Me.TabControlMain.Value Then
        If VarCtlSubform.SourceObject <> "" Then
            VarCtlSubform.SourceObject = ""
        End If
    Else

        If VarCtlSubform.SourceObject <> VarCtlSubform.Tag Then
            VarCtlSubform.SourceObject = VarCtlSubform.Tag

            strSubForm = VarCtlSubform.Name
            Set sbf = Screen.ActiveForm.Controls(strSubForm)
            sbf.Form.AllowAdditions = True
            sbf.Form.AllowEdits = True
        End If
    End If
Next

End Sub

回答5:

Your application is trying to open too many connections to the Access database. Its not just the tables in your sql statement that add up to 2048, even the forms, reports, comboboxes, unclosed recordsets etc add up to the number of connections used by your application. Few things you can try out here:
1. Close the resources (eg record sets) which you are not really using.
2. If you are making use of domain aggergate functions( eg DLookup), change it with Elookup as it explicitly cleans up after itself.
3. You can modify your sql code to make use of Temp Tables.
Hope it helps.

回答6:

You need to evaluate each section of your UNION query, and any other queries that it depends upon. You may get improvement by creating a temp table that represents a query with many joined tables, and use the temp table instead.

When I started developing with Access I had a habit of making big denormalized snowflake queries and using them as a source for a reports and listboxes. I didn't have any tables with more than 100,000 records and the database ran fast. Later I started to get the annoying "Cannot open any more databases" error and discovered the errors of my ways.

I created a form that will help track how many databases connections you have used and how many remain. If you add this form to your database and click Requery after opening your queries and other objects, you will be able to find the objects that are using a significant number of connections.

Note that every reference to a local table or query object uses 1 connection. A reference to a linked table uses 2 connections. A query that joins two linked tables will use 5 connections. If that query is getting called by many other queries in your union, the number adds up fast. Maybe you don't need any of the fields from a joined table in a subquery. In that case you can make a new query.

I read a lot about this online and some people think Access/Jet has around 2,000 TableID’s but that number doesn’t match up with what my form reports. The numbers reported by my form align perfectly with the error. It may be counting something different than TableID’s but it provides an accurate gauge to measure the amount of connections being used as you open new objects.

You can read more and download it from here:

https://access.wordpress.com/2014/04/01/how-many-database-connections-are-still-available-in-an-access-database/