Runtime error '3048'cannot open anymore databases 1

[ScubaStevo]

Hi

I can this error message when trying to open a form many times in a loop and append records to a table. after 125 records are added, i get a pop up message saying:

Runtime error '3048'cannot open anymore databases

how can i avoid this?

Thanks

Steve

 

[PHV]

Without seeing the code it's hard to say anything ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Trevil]

About a year ago, I got involved with an application that had MANY aborts due to this error. I'll look for my notes, but the following is the general idea:

1. If you open it -- then close it when not used. This applies to all recordsets, queries, etc.
2. There is a limit in Access to how many tables can be opened (maybe 180? of course I don't remember). Each table takes on entry unless it's linked, then it takes two.
3. If you have a few monster queries that seem to open every table, you need a better solution. We found it necessary to sometimes create a temp table with the query, then reference the temp table.

We eventually got rid off all errors, so I'll look for my notes...

"Hmmm, it worked when I tested it....

 

[Trevil]

Additional question/suggestion - if you use a lot of code that does stuff like: Set rs = currentdb.openrecordset...
then define a global variable and use it instead. i.e.
Global dbs as DAO.Database

then:
Set rs = dbs.Openrecordset...


Additional Info from my notes:
Error 3048 – Cannot Open Any More Databases

. With attached tables, you can open only 84 recordsets using “CurrentDB” (one table per recordset) before receiving the error.
· You can open over 200 recordsets using “DBEngine”
· You can open over 200 QueryDef’s using “CurrentDB”
· After opening 200 QueryDef’s, I was able to only open 50 recordsets using “CurrentDB” with One table
· After opening 200 QueryDef’s, I was able to only open 50 recordsets using “CurrentDB” with three tables
· Could only open 50 recordsets based on query with 3 tables.
· Could only open 200 recordsets using “DBEngine” based on query with 3 tables.


"Hmmm, it worked when I tested it....

 

[ScubaStevo]

I am using the code:

Set dbs = CurrentDb
Dim [TableName] As Object
Set [TableName] = dbs.openrecordset([TableName])

Then I append records using:

[TableName].AddNew
[TableName]![FieldName] = [Value]
[TableName]![FieldName] = [Value]
[TableName]![FieldName] = [Value]
etc
[TableName].Update

I append approx 20 records like this, and this code is in a Do While loop so it starts again. It needs to loop around over 3000 times, but instead it gets to 125 iterations (where each iteration appends 20 records) then the error message stated above is displayed.

Instead of append the records in VBA, I used individual queries so I didnt need the:
Set dbs = CurrentDb etc
but I can only run the set of queries 125 times as well, leaving me in the same spot of bother.

Is there a way to aviod this, or a better way to append so many records?

 

[ESquared]

DoCmd.RunSQL "INSERT TableName SELECT Value, Value, Value, Value

 

[ScubaStevo]

I tried the RunSQL statement instead of methods above and I still get the same outcome, it will only loop 125 times and say "Cannot open any more databases." Any other suggestions??

 

[ScubaStevo]

Also, I tried the:

dbs as DAO.Database

from Trevil above and that looped through all records but did not append any, so what do I have to append records with this method?

 

[Eupher]

ScubaStevo,

Can you show us the whole code? I'd like to see where the recordsets are opening in relation to the loop...

Ken S.

 

[ScubaStevo]

Certainly. This loop opens a form and uses the PatioNo:


[PatioNo] = 100

Do While ([PatioNo] < 3233)

stDocName = "CostGridsTakeoff"
DoCmd.OpenForm stDocName, , , stLinkCriteria

stDocName = "Close CostGridsTakeoff"
DoCmd.RunMacro stDocName

stDocName = "Update CostGrids_temp ItemCosts"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Update CostGrids_temp TotalCost EA"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Update CostGrids_temp TotalCost LM"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Append CostGrids"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "Delete CostGrids_temp"
DoCmd.OpenQuery stDocName, acNormal, acEdit

[PatioNo] = [PatioNo] + 1

Loop



The form that opens contains a lot of code, but the procedure that appends the records starts is:


Set dbs = CurrentDb
Dim CostGrids_temp As Object
Set CostGrids_temp = dbs.openrecordset("CostGrids_temp")



' External Truss
[CostGrids_temp].AddNew
[CostGrids_temp]![PatioNo] = [PatioNo]
[CostGrids_temp]![Decking] = [Decking]
[CostGrids_temp]![ExternalTruss] = [ExternalTruss]
[CostGrids_temp]![InternalTruss] = [InternalTruss]
If ([TrussMaterial] = "76381.6") Then
[CostGrids_temp]![Beam] = "76"
Else
[CostGrids_temp]![Beam] = "100"
End If
[CostGrids_temp]![Width] = [Opening]
[CostGrids_temp]![Length] = [Depth]
[CostGrids_temp]![Area] = [Area]
[CostGrids_temp]![Itemcode] = [ExternalTrussIC]
[CostGrids_temp]![Quantity] = [ExternalTrussQty]
[CostGrids_temp]![ItemLength] = 1
[CostGrids_temp]![UM] = "EA"
[CostGrids_temp].Update


' Internal Truss
If ([InternalTrussQty] > 0) And ([InternalTrussQty] <> "") Then
[CostGrids_temp].AddNew
[CostGrids_temp]![PatioNo] = [PatioNo]
[CostGrids_temp]![Decking] = [Decking]
[CostGrids_temp]![ExternalTruss] = [ExternalTruss]
[CostGrids_temp]![InternalTruss] = [InternalTruss]
If ([TrussMaterial] = "76381.6") Then
[CostGrids_temp]![Beam] = "76"
Else
[CostGrids_temp]![Beam] = "100"
End If
[CostGrids_temp]![Width] = [Opening]
[CostGrids_temp]![Length] = [Depth]
[CostGrids_temp]![Area] = [Area]
[CostGrids_temp]![Itemcode] = [InternalTrussIC]
[CostGrids_temp]![Quantity] = [InternalTrussQty]
[CostGrids_temp]![ItemLength] = 1
[CostGrids_temp]![UM] = "EA"
[CostGrids_temp].Update
End If

' Purlins 1
[CostGrids_temp].AddNew
[CostGrids_temp]![PatioNo] = [PatioNo]
[CostGrids_temp]![Decking] = [Decking]
[CostGrids_temp]![ExternalTruss] = [ExternalTruss]
[CostGrids_temp]![InternalTruss] = [InternalTruss]
If ([TrussMaterial] = "76381.6") Then
[CostGrids_temp]![Beam] = "76"
Else
[CostGrids_temp]![Beam] = "100"
End If
[CostGrids_temp]![Width] = [Opening]
[CostGrids_temp]![Length] = [Depth]
[CostGrids_temp]![Area] = [Area]
[CostGrids_temp]![Itemcode] = [PurlinsIC]
[CostGrids_temp]![Quantity] = [PurlinsQty1]
[CostGrids_temp]![ItemLength] = [PurlinsLength1]
[CostGrids_temp]![UM] = "LM"
[CostGrids_temp].Update


There is about 20 of these append blocks in this procedure and it ends.

 

[Eupher]

What does the macro "Close CostGridsTakeoff" do?

Ken S.

 

[ScubaStevo]

It's not meant to be there, was just trying something. It did close the form that was opened previously (CostGrids_temp), but yeah not there now.

 

[Eupher]

Yikes! So when does the form "CostGridsTakeoff" ever get closed?

Ken S.

 

[ScubaStevo]

The form has an onload function that performs some calculations, appends the records (as per the code above) and then closes with "DoCmd.Close". Then It goes back to the loop to continue.

 

[RoyVidar]

This approach seems a bit strange, sorry to be so direct, but why on earth do you open a form to run some code, then close it? And this within a loop?

Why not place all your code in a module, then run it there? This would give you much more control over the process - and (my opinion, sure) get rid of that macro thingie, convert it to VBA, so you can have some control over it.

I'm guessing (or close to betting), that somewhere along this rather convoluted route, there are memory leaks, some objects which are not closed or properly released...

I don't think I'd go late bound on DAO, but rather ensure there's a reference to DAO (in VBA - Tools | References), then use

[tt] Dim CostGrids_temp As DAO.Recordset
Set CostGrids_temp = dbs.openrecordset("CostGrids_temp")[/tt]

Same with the openquery stuff, I'd probably rewrite it to execute the queries in VBA. Not that I use DAO much, but basically I think something like fetching the query (assigning to a querydef object), resolve parameters if any, then execute, should be easily found through a search.

There's no need to use [brackets] around variable names.

[tt] [CostGrids_temp].AddNew
' should/could be
CostGrids_temp.AddNew[/tt]

I wouldn't be surprised if more of what you're doing here, could be performed through executing queries, in stead of using recordsetapproaches. Executing queries are more effective.

If the recordsetapproach is really necessary, then stuffing it all into one module, would enable you to keep the recordset(s) open, in stead of opening/closing all the time, which may be one of the reasons for the current challenge. Especially if there are issues with regards to the objects not being properly closed/released.

Roy-Vidar

 

[ScubaStevo]

I open the form because it has a query as the record source. I would do it all from VBA but I have no idea how to run a query from VBA and use the values it returns.

From the query, a heap of calculations are performed and the resulting values are stored into a new table (CostGrids_temp). I have tried using many different methods to append these records, from individual queries, the AddNew code from above and also SQL from a module. They all get stuck at the same point (and there i nothing wrong with that particular record as I've tried starting after that record and it still stops after the same number of iterations).

So if you think that running a query in a module (and manipulating the values it produces in the module) rather than using a form is the way to go, please show me how.

Thanks
Steve

oh, i looked for the DAO in VBA Tools | References and couldn't find which reference its corresponds to?

 

[RoyVidar]

Microsoft DAO 3.6 Object Library.

A search using the terms I provided (querydef execute) did return some threads in this forum that might be of value, here are a couple from the first page of results thread702-946750, thread702-917700, thread702-794890, though, to return a recordset from a query using DAO, look for the OpenRecordset method used on a querydef object, not the execute method (which executes the query in stead) - then there are other relevant fora to search, forum705, forum181 and forum701.

Try some of the approaches there, "stuff" your form recordse into it, and see what it produces... (do work on this in a copy, not the production db)

I don't think I'm going to attempt a rewrite of the complete code...

Roy-Vidar

 

[Trevil]

Aside from the fact that I agree it is *strange* to have a loop that keeps opening your form, I still believe you are missing a close / release for one or more recordsets.

You posted some code from your form where you have:
Set CostGrids_temp = dbs.openrecordset("CostGrids_temp")

But you did not post any code that shows you close it. If you don't have it, then I strongly suggest you add to your form:
CostGrids_Temp.Close
Set CostGrids_Temp = Nothing

Let me know what happens.

"Hmmm, it worked when I tested it....