'Cannot open any more databases' error

[mcalex]

Hi, I have a macro that produces a report that uses a number of queries to populate summary data on p1 and detail data on each of the following pages.

I recently split the database and now, when the macro is run, it stops 3 times with a 'Cannot open any more databases' prompt. On clicking OK, the report continues, but when it is finished, there are 3 subreport 'boxes' or sections that are blank on the summary page - no labels or data are printed at all.

Being somewhat new to access I don't know the best way to fix this. My thoughts so far:
1) Move the macro to the back end. Given that it worked before I split the database, I'm assuming the connection is the problem and so if I put the macro in the backend where the tables are, it will work as before. Is this correct if so, how do I move the macro?
2) Increase the maximum number of databases that can be opened at one time. (I'm working on the theory that some MS programmer once said 'nobody's ever going to need more than 640kb of databases open' or similar). I cannot find this property in Tools/Options, so it may be a faint hope, but can someone confirm this?
3) Web searches seem to indicate combo-boxes can be a culprit of this error message, depending on rowsource values. The macro doesn't use combo boxes, but if looking up data in other tables in where clauses has the same effect, this may make sense. Is there something I can do to test this?
4) (The most realistic presumption, imho) Somewhere the macro is opening new databases where it should be using databases already open. Either that, or the databases should be closed when they are finished with. (I'm guessing - given that we only have one database - that the error message is actually referring to connections or table handles rather than actual databases.) How would I go through and find if this is the case?

tia for any help

 

[DCrake]

I'm beginning th think you have some conflicting information coming from Access. There is a topic on this on the forum but can't put my hand on it. However what I would try first is to create a blank database and import all the objects in it, except the linked tables. Then use the link table manager to relink your data. Retry, se if this solves it.

David

 

[Poppa Smurf]

There was an item posted at the end of February this year it may give some ideas. Here is the link
http://www.access-programmers.co.uk/forums/showthread.php?t=166873&highlight=open+databases

 

[mcalex]

Hi DCrake,

tried what you said, but in reverse order. Ie, took a blank copy of the Front End (just the linked tables) and imported the macro and then ran it, importing each new bit (query, report, macro, whatever) it needed as it asked for them. In the end it hit the same problem. Will try again in the morning, but in the order that you said, ie import the objects, then link in the tables.

Hi Poppa Smurf

had a look at that thread, hmmm, seemed the OP came up with a work around that required taking a table out of his query. Not the most satisfactory solution, lol. One of the points that did strike a chord was the non Access BE. We have some Access tables, but most are linked in from a sql-server database. In fact, I think the access tables are just lookups into the tables from sql-server anyway.

I don't think the discussion on combo boxes is relevant to me as there are no combos being used in the macro, report, subreports or queries.

When you run a query, is there an explicit way to shut down the db connection? From my understanding, it's not necessary with select queries, but there may be an update query or two running around in the macro.

I'm pretty sure the design of the macro has some err, everything to do with it. I only got an idea of how complex it was during the import process I mentioned in my reply to DCrake. The macro calls other macros, a whole bunch of queries, and about half a dozen sub-reports to make the report. Could be time to take it apart, into little pieces and see what it's doing wrong.

Any other suggestions most appreciated.

 

[The_Doc_Man]

In general, the "can't open any more ...widgets" message is telling you that you cannot open more widgets until you close some of them.

Is there a way to make the elements of your reports close their data sources in some sequential manner so that while you are building at sub-section 1, you don't open anything else, then close the DB for that section when you are building sub-section 2? And so on?

 

[SOS]

mcalex:

Are you using any code like ADO to pull information? Are you using any CONNECTION type objects?

 

[shadow9449]

I had this problem once when I split a database. I.e. before it split, everything opened smoothly. As soon as I split the database, I got this message.

While I have no idea why there should be a difference in how many databases are open between a split database and a non-split, I was able to solve the problem by doing a few things that I should have done anyway.

For example, I had a number of combo boxes on a form that got their data from queries. I removed the record source and programatically set the record source of those boxes to the SQL statement as the user enters the field. All it took was a cut and paste and the form loaded noticeably faster.

The next thing I did was noted that there was a form open that had subforms on a number of tabs, each drawing from a different table (linked to the main form). Again, the program is drawing all that information when the user may only need to use 1-2 tabs per time s/he opens the form. So, I used some code to only load those subforms when the user opens them. (Bob Larson has a sample of this technique. I didn't quite use his technique but I created my own that works fine).

Well, sure enough the form loads a lot faster now and I don't get the message you are receiving.

I hope that this gives you some ideas.

SHADOW

 

[mcalex]

Hi again.

OK, solved, or at least, working.

When I investigated, I found that the report was using a certain query as its record source. Subreports were using additional queries. Those queries were then also using the report's main query. That main query has at least three levels of nested queries, and uses the same subquery at different levels of the nesting (recursion, anyone?). In short, a nightmare

So now I've changed the macro to create a temporary table and the nested queries use that instead of the original query. A case of - as shadow9449 pointed out - sovling the problem by doing a few things that should have been done anyway.

It feels like I've only fixed a small part of the problem, but its definitely steps in the right direction, and it's working, so I'm happy. I'm in the middle of trying to redesign the database (which will mean rewriting all the queries and that report) so 'working' is good enough for the moment.

Thanks for all the pointers

mcalex