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

Asked 25/9, 2012 at 8:41 Answered 2/11, 2018 at 19:53

Solved sql excel ms-access ms-access-2010

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?

Cochlea answered 25/9, 2012 at 8:41 Comment(7)

Wow, 2048 tables.. out of interest how did things get so bad? – Beaston 25/9, 2012 at 9:6

There are not that many tables in the DB, of course. There are fewer than 50 tables, and about 100 queries. – Cochlea 25/9, 2012 at 12:37

Despite Kieren's highlight, I'm amazed that none of the provided answer's lean more towards a serious re-think of the UNION sql, if the data structure is optimised well then you should never need to end up with such a huge query. Especially bad in access as it all runs locally and could take a lifetime. – Parlous 25/9, 2012 at 14:41

That's probably because my question was if there are other ways than killing the unions :). I'm just not very keen on optimizing the whole structure to avoid the unions. – Cochlea 25/9, 2012 at 15:7

Yeah Unions are a special case situation and are 90% of the time used by novices to perform simple tasks of which they end-up producing massive datasets, well after they are gone, and over time are unreliable on information returns when used incorrectly – Putrefaction 27/9, 2012 at 13:28

Performance tip: UNION ALL is much faster and uses less overhead than UNION if the queries you are joining do not have duplicates that you want removed. – Infinitude 27/2, 2019 at 0:56

Yep, most of those unions used ALL. But it does not help with the number of open "databases" (either way). – Cochlea 27/2, 2019 at 11:32

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.

Schnorrer answered 25/9, 2012 at 13:40 Comment(2)

The German message is Fehler 3048: Mehr Datenbanken können nicht geöffnet werden btw. – Schnorrer 25/9, 2012 at 13:58

Wow, and it get's magnitudes faster to edit and execute the queries this way. It's a bit crappy that Access won't handle this on it's own. But for my case this solution is ok. – Cochlea 25/9, 2012 at 14:15

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.

Snelling answered 25/9, 2012 at 10:50 Comment(3)

There are no forms or vba code involved, actually. Opening the accdb and execution one (specific) query is enough to pop up the error message. If I put the results of the depending queries into a table and use those by a modified query version it works. – Cochlea 25/9, 2012 at 13:46

Your query may call aggregate or VBA functions. Can you post the sql of this query? You mention specific query. Only with this query? Also with other queries? – Snelling 25/9, 2012 at 16:9

The code of the query is like SELECT * FROM A UNION ALL SELECT * FROM B... - but msjav's answer solved the actual problem very well. – Cochlea 25/9, 2012 at 16:15

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/

Infinitude answered 2/11, 2018 at 19:53 Comment(2)

You can download an updated version of my Available Connections utility here. – Infinitude 5/9, 2019 at 18:56

Many thanks for the 'Available Connections' utility. Makes sense of of lot of strange behaviour I was getting with complex Access UIs. – Hilaire 30/4, 2020 at 9:42

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.

Schwinn answered 25/9, 2012 at 9:2 Comment(2)

Uh, I did not understand this answer before. I guess you meant the same like msjav? – Cochlea 25/9, 2012 at 15:5

Yes, but my answer wasn't very clear... should have used examples really, I will include examples in future to help :) – Schwinn 22/10, 2012 at 10:2

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

Wyoming answered 10/3, 2015 at 18:17 Comment(0)

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.

Burushaski answered 25/9, 2012 at 9:7 Comment(1)

Closing resources won't help, I guess. I can start the application, execute one query (no vba) and the error occurs. How can temp tables help here? Can you make an example? – Cochlea 25/9, 2012 at 13:43

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags