Help managing open connections / "cannot open any more databases" error

[cricketbird]

I am trying to loop through all the records from a query and print one PDF report for each line of the query results. The data is located on a networked backend.
The code below generates the dreaded "cannot open any more databases" error after the first report. I was told each report opened creates connections to the backend. I thought I could maybe fix this by closing the report after each run. Indeed, after adding in the "DoCmd.Close acReport" line below, it now will generate about 25 reports before throwing that same error. I have hundreds of reports that need to run, though.

Is there something I can do (beyond closing the report) to help close connections once they aren't needed?

Set MyRs = CurrentDb.OpenRecordset("BackupCardListQry")
With MyRs
    .MoveFirst
    Do While Not .EOF
        fileName = !Name & " - Card #" & !CardID & " - " & todayDate & ".pdf"
        DoCmd.OpenReport "RptPERMANENT_CARD", acViewReport, , "[CardID] = " & !CardID
        DoCmd.OutputTo acOutputReport, , acFormatPDF, thisLoc & fileName
       DoCmd.Close acReport, "RptPERMANENT_CARD"
        .MoveNext
    Loop
End With

I am running MS Access Version 2301 (build 16026.20238 Click-to-Run) with no options in my corporate environment to change/downgrade/upgrade versions. Based on what I've read about the "cannot open any more databases" error, I've added the front and back ends to my "trusted locations". That has not made any noticeable differences.

 

[CJ_London]

Perhaps try this alternative method

Looping through a recordset to output Ms-Access report to a pdf file

I'm trying to get pdf file by tenant_id using docmd.outputTo. Unfortunately this routine produce output pdf file that all are in a same single tenant_id. If I remove docmd.outputTo last parameter

stackoverflow.com

 

[The_Doc_Man]

Our member isladogs has posted on this problem on another forum, so I have the link here.

Cannot Open any more databases

I have built an Access database that is throwing up error code 3048; "Cannot open any more databases". It has been doing it for a couple of years. I am..

techcommunity.microsoft.com

The Connection object is a member of the Connections collection of the DAO Workspace object. It would be possible to examine the collection and there is even a <dao-connection-object>.Close method, but the danger is knowing which connection to close. However, another thought comes to mind. You are in an event-based loop that is opening a bunch of things and closing them, but Access tends to queue up actions for later. Maybe if you stuck a DoEvents after the DoCmd.Close acReport, ... it would allow the file handles, connections, and other internal structures to settle down a bit. In that other thread, it makes it clear that the connections implied by the open report DO go away. The down side is that your loop might run a little bit slower due to spreading the overhead through each loop step rather than waiting for your event to exit.

 

[cricketbird]

Thanks @The_Doc_Man! - @isladogs's post is what I based my original loop in the first place. I tried adding the DoEvents line, but it doesn't seem to make a difference, unfortunately. I might play more with closing connections if I can figure out the right ones. This seems like the sort of "garbage collection" that Access ought to do itself, but it's a good challenge

 

[CJ_London]

have you looked at the link in post#2?

 

[The_Doc_Man]

When you get that error, do you also get the option that allows DEBUG or ABORT? If you take the Debug option, you should be able to see the line that is giving you the problem, highlighted in yellow. Which actually doesn't matter because you can guess it is the line that opens the report for you. BUT taking the DEBUG option gives you the opportunity to explore from the Locals window to look for an accumulation of objects to SEE what you have run out of. From the VBA window, the View >> Locals option lets you see something that starts out looking very simple.

If you click in the Plus box next to Me, you see

Click on the Application's Plus (actually, for tree views, it means "Expand").

From here, you can expand on the DBEngine (I'll spare you having to look at more of my diagrams) and go exploring. When you open the DBEngine, you can see things like Recordsets. When you drill down far enough, they are all "Item" plus a number, because that is the nature of collections. You probably don't need to look at the details of each item (though it CAN be enlightening). What will matter is how many of them there are. If you find a collection with 255 items, that will be the one that is eating your lunch for you. Recordsets should be there. From the Workspace object, you should see Connections. There might be a few others, but those two are the top of the list for becoming clogged.

 

[isladogs]

I don't have time to get actively involved in this thread.
However, its a pity you can't upgrade to version 2305 as MS have just doubled the number of available connections.
I've just updated my article on this topic today

Available Connections Add-In

This is an Access add-in used to monitor the number of open databases/tables and help prevent 'cannot open any more tables' or 'cannot open any more databases' errors.

www.isladogs.co.uk

The change means there should be far fewer 'cannot open any more tables/databases errors, but it still good practice to close all objects when not in current use. This will free up available connections

Also, version 2305 fixed this specific bug
Error 3048 "Cannot open anymore databases" when exporting many reports to PDF.

See the MS bug fix article at
Access Releases 14 Issue Fixes in Version 2305 (Released June 1st, 2023) (microsoft.com)