Error 3048: Cannot open any more databases

[Cheesewoman]

I have been building an Access Database (2010) at work for a good few months now (on and off), and I have tried my best to keep to best practices where possible. It currently has;

16 Tables (split into a back end database, and linked to the front end database)

13 Queries, none of which are that complex, 8 of which run before the database is even loaded for use.

7 Forms, 3 of which are subforms on the main form (again, none of these forms are complex by any means, a couple of buttons, list box, a few text boxes etc).

4 Reports which all run from queries (with modified filters through VBA).

It was all working fine until I added the reports, and since then whenever I try to use the main form, after tabbing onto a particular textbox triggering the code below (note that I use temp tables as I have some behind the scenes binding going on here) it always breaks and throws the 3048 error "Cannot open any more databases":

    Dim rs As Recordset
    Set rs = db.OpenRecordset("SELECT * FROM BatchTemp")

I have attempted to look this up on google and follow other people's fixes, but to no avail.

My understanding of this is that I have too many connections open, be it through queries, tables, forms controls etc. at the same time.

So I have gone through and made absolutely sure that I have closed and cleaned up all of my database connections, recordsets etc. Nothing is left open, and there are no infinite loops. In fact the code hasn't changed at all for the forms (apart from the report form, which doesn't trigger this issue) from before I put the reports in.

I was running each report through it's own form before, and I've now condensed that down to one form for all 4 reports, which actually makes it a lot simpler.

Despite my efforts I am still getting this very annoying and difficult to debug error.

You guys have helped me out a lot in the past and I'm hoping you can do so again today please!

I think I might lose all of my hair by the end of today if I don't get this fixed :banghead:

 

[Ranman256]

you did not define DB in db.openrecordset...

use: set rs= currentdb.openrecordset

(i have a hundred tables, and hundred queries and it works)

 

[Cheesewoman]

you did not define DB in db.openrecordset...

use: set rs= currentdb.openrecordset

(i have a hundred tables, and hundred queries and it works)

Thanks for the reply

Actually I have defined it, it's just that I've only shown you the line that the code fails on, which I don't believe is actually the cause of the problem anyway...

I agree that having the amount of tables and queries and forms that I currently have is not much, and it should work!

I didn't mention before that I have also tried a compact and repair, but it didn't work either.

 

[JHB]

Create a new database and copy all into it except the report which cause the problem. Does the database run okay then, so try to create the report again from scratch.
Else post a stripped down version of your database with some sample data.
When you show code the show it all and not only a snip of it!

 

[Cheesewoman]

Create a new database and copy all into it except the report which cause the problem. Does the database run okay then, so try to create the report again from scratch.
Else post a stripped down version of your database with some sample data.
When you show code the show it all and not only a snip of it!

My apologies - I didn't think that the code snippet was really vital in this case so I only wanted to show the bit where it fell over

Anyhoo, I tried what you suggested and copied everything into new databases (apart from the reports) and still ran into the same issue.

I also tried to merge the front and back ends back together again (seeing as it's a split database), which seemed to solve the issue. However when I re-split the database it comes up with the error message again. I do need them to be split as it will be running across a network with multiple users on multiple machines - and also different interfaces for different users (sigh).

I'm not sure why the split would cause this to happen, but I'm gonna look into it and will post back if I find the answer. Of course if you have any more wisdom please do share

 

[The_Doc_Man]

The problem might by solved by understanding that old book, "Everything I Need to Know I Learned in Kindergarten." The key phrase here, I think, is: If you open something, you must remember to close it.

If your code snippet to open that database gets executed in a repetitive context, it opens a new database with each repetition. If this happens "behind the scenes" in a form, that action occurs in the context of a class module. A class module will dissolve when the form closes, and unless the module's code takes great pains about where it stores things, ALL OF THE VARIABLES GO AWAY, TOO.

Where this becomes an issue is that the variables defined by using the syntax

SET object-variable = compatible-object

are dissolved on class-module closure, but these object-variables are POINTERS to the structures in question, and they are defined in your program heap (a memory scratchpad area). If you dissolve the pointer but don't close the structure first, you leave the object open in the heap where it consumes some kind of resources. If you do that enough, you run out of those resources and get the error message you reported.

If you remember to explicitly close each database that you open before you let your form go away, this problem should radically improve.

 

[Cheesewoman]

Sorry for the delay in posting, thanks for all the help so far. I'm actually pleased (and very relieved) to say that I've actually found where the issue was and managed to fix it.

It turns out I did have an "infinite loop" in my code that was opening a connection over and over again until it bummed out with the error - I was trying to make a subform act as part of the main form by limiting it's view to 1 record using vba (I kinda hacked it together), but I didn't realise there was a "cycle" property for this and now that I've updated the code it no longer causes the error (and it's much neater!)

I'm glad I wasn't going mad - I knew I had closed everything I'd opened, just didn't notice that loop before (it never fired before so I don't think I really knew it was there!)

Thanks again for all the help.