Solving Error 3048 - Cannot open any more databases

I am working with a donations database, and have developed a tax deductible receipt as a report.
The receipt prints both to paper and to a pdf in a folder. One of the problems is that there are several versions of the report, and so rather than create several reports, I coded the vba in the OnOpen event to make changes in the report according to data. Example: If a qualified donee gives a donation, then the receipt cannot claim to be an official receipt for income tax purposes, and instead says that it is an acknowledgement of gifts from the qualified donee. There are other changes to the visibility of certain labels depending on whether it is a reprint or a lost receipt, on whether it s a Gift in Kind, and on whether the gift contains an advantage received by the donor.

So the problem is that part of the code causes Error 3048, "Cannot open any more databases". I have done a lot of reading on it and found that something happens in Access 2016 which did not in previous versions. There have been various solutions given, none of which seem to work. Here is the code (sorry not sure how to put it in its own window thing). I removed some for simplicity and added 3 dots to show what was removed. The part where it errors out after 75 receipts are printed, is here at the command lline "DoCmd.OutputTo acOutputReport".

Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM BaseQry_ViewRcpts WHERE RcptSent = False ORDER BY RcptBatchID;")
If rst.RecordCount = 0 Then
MsgBox "There are no other unprinted receipts from those dates."
Exit Sub
End If
rst.MoveFirst
... /code to get receipt numbers for details when receipt opens/ ...
DoCmd.OpenReport "Donation_Rcpt", acViewReport, , , acHidden
Report_Donation_Rcpt.Requery
... /code to set name of PDF following/ ...
DoCmd.Close acReport, "Donation_Rcpt", acSaveNo
DoCmd.OutputTo acOutputReport, "Donation_Rcpt", acFormatPDF, "C:\Users\Camp.Director\Documents\New Tax ReceiptsTEST" & KeepName1 & ".pdf"
... / edit - update record to show as printed / ...

• Some said there was an endless loop where things aren't being closed. I tried to solve it with that in mind, and it isn't that.
• Some said that the issue was a programming error on attempts to DoCmd.OutputTo acOutputReport to a PDF and that there is an update for it. My version of Access uses volume licensing, and does not get those updates automatically, and I have failed at trying to find and install them manually. :(
• One said that the report needs to be closed before the DoCmd.OutputTo acOutputReport occurs, which is why the last two lines are in that order. Unfortunately when I do it this way, all the changes made on the OnOpen and Requery events are lost and the report is no longer accurate. But it does solve the error. I ran over 100 receipts with no code errors occuring.

I think that somehow, when DoCmd.OutputTo acOutputReport runs internally with the report open, it somehow opens a file in memory and doesn't know how to close it. At one point I had used DoUntil rst.EOF - MoveNext - Loop to make the code smoother, and when I saw the possibility of endless open files, I chose to remove the loop, and find another way to go back until no more records showed up in the Select query. So after printing on paper and then on PDF, I closed the report, and then did a rst.close and db.close before going back to the top and starting over for the next unprinted record. It solved nothing. The only thing that does is to close the report before the PDF. But then my data is not accurately modifying the report.

Thanks for any help.
Gary

What happens if you open the query recordset, print the report, close the report and close the query recordset and set it to nothing.
Then re-open the query recordset, output the report to pdf and then close the query.

I have now tried it, and it does get through all the records, but it doesn't provide accurate PDFs. To make it accurate I need to requery. Then it stops at 75 records.
In brief... (mixing code with a summary of code)
Set rst = select query
Open MyReport (using acViewReport, , , acHidden so it doesn't show or print)
MyReport.Requery (so it fixes the On Open changes)
Open MyReport (using acViewNormal, , , acHidden so it prints)
Close MyReport
rst.Close
Set rst = nothing
Set rst = select query (same as before basically, this time to print to PDF)
Open MyReport (using acViewReport, , , acHidden so it doesn't show or print)
MyReport.Requery (so it fixes the On Open changes)
DoCmd.OutputTo acOutputReport, "Donation_Rcpt", acFormatPDF, fileaddress
rst.Edit
rst![RcptSent] = True
rst.Update
Close MyReport
rst.Close
Set rst = nothing

When I leave out either requery, that set doesn't print accurately. But all records print if second one is only one missing.
As soon as I add that second report requery, the accuracy is there but it crashes at the 75th record.

I have tried a lot of angles and am beginning to think I need to make 4 reports, and use the data to choose which one to open. That way I don't need the requery.
Let's see..
1. Normal Tax Receipt
2. Tax Rcpt for Gift in Kind
3. Tax Rcpt for Gift in Kind with an advantage to the donor (a Board motion could get rid of this one)
4. Tax Rcpt for Gift that includes an advantage to donor (such as auction fundraiser)
5. Acknowledge Gift from Charity

Four receipts seems less convoluted.

On second thought, I'd need a canceled receipt for each of the four, plus a replacement receipt for each. So that is 12. :(

Alternatively I can also create several fake fields that I put into the query prior to selecting it, and mathematically use data to populate the fields which are actually labels on the report. Convoluted.

Hoping the bit about the opened report requery is somehow causing the output to pdf to have too much open will connect with an idea. :)

Gary

I am concerned that it is crashing on the 75th record.
What is the error?

I found a tool that counts the number of connections available in an access db. I downloaded it and placed the form in the database. I then made the respective sub public and called it at various points below as you can see [Form_Frm_dialog_Available_Connections.SUpdateRemark]. I then took each respective result and placed it into the code after an arrow so you can see where the connections are being used and then freed up again.

249 initially available at 2020-02-19 5:01:29 PM
Set rst = db.OpenRecordset("SELECT * FROM BaseQry_ViewRcpts WHERE RcptSent = False ORDER BY RcptBatchID;")
If rst.RecordCount = 0 Then
MsgBox "There are no other unprinted receipts from those dates."
Exit Sub
End If
rst.MoveFirst
RemContactID = rst![DonorID]
MyRcptNo = rst![RcptBatchID]
Form_frm_dialog_Available_Connections.sUpdateRemark --> 242 available
DoCmd.OpenReport "Donation_Rcpt", acViewReport, , , acHidden
Form_frm_dialog_Available_Connections.sUpdateRemark --> 228 available
Report_Donation_Rcpt.Requery
Form_frm_dialog_Available_Connections.sUpdateRemark --> 228 available
DoCmd.OpenReport "Donation_Rcpt", acViewNormal, , , acHidden
Form_frm_dialog_Available_Connections.sUpdateRemark --> 228 available
DoCmd.Close acReport, "Donation_Rcpt", acSaveNo
Form_frm_dialog_Available_Connections.sUpdateRemark --> 242 available
rst.Close
Form_frm_dialog_Available_Connections.sUpdateRemark --> 248 available
Set rst = Nothing
Form_frm_dialog_Available_Connections.sUpdateRemark --> 248 available
Set rst = db.OpenRecordset("SELECT * FROM BaseQry_ViewRcpts WHERE RcptBatchID = GetMyItemizedRcpt() ORDER BY RcptBatchID;")
Form_frm_dialog_Available_Connections.sUpdateRemark --> 242 available
rst.MoveFirst
KeepName1 = rst![IssueName] & "." & Right(rst![ReceiptNo], 3) 'Prepare file name for PDF print
DoCmd.OpenReport "Donation_Rcpt", acViewReport, , , acHidden
Form_frm_dialog_Available_Connections.sUpdateRemark --> 228 available
Report_Donation_Rcpt.Requery
Form_frm_dialog_Available_Connections.sUpdateRemark --> 228 available
DoCmd.OutputTo acOutputReport, "Donation_Rcpt", acFormatPDF, "C:\Users\Camp.Director\Documents\New Tax ReceiptsTEST" & KeepName1 & ".pdf"
Form_frm_dialog_Available_Connections.sUpdateRemark --> 225 available
rst.Edit
rst![RcptSent] = True
rst.Update
DoCmd.Close acReport, "Donation_Rcpt", acSaveNo
Form_frm_dialog_Available_Connections.sUpdateRemark --> 239 available
rst.Close
Form_frm_dialog_Available_Connections.sUpdateRemark --> 245 available
Set rst = Nothing
Form_frm_dialog_Available_Connections.sUpdateRemark --> 245 available
Code is resent to top until no receipts are remaining.

The second time around every dialog came back as 3 less than the previous one from the same spot.
It Errors 75 records later, during the attempt to DoCmd.OutputTo… which is 23 less than the beginning of that round
75 * 3 = 225 and 225 + 23 = 248 So it has no connections left and does not allow the action to occur. I could be one rotation out as well, meaning that it may try to drop to -3 at that point. Either way that is why it errors.

When you study the places the available connections drop and then rise again, you can see that:
rst.close compensates for set rst = db.OpenRecordset
DoCmd.Close acReport compensates for DoCmd.OpenReport
But nothing compensates for DoCmd.OutputTo acOutputReport
There should be a DoCmd.OutputTo acCloseOutputReport

I removed the second report requery and ran it again, and here is a chart of the important points, for both first and second runs, both ways. I am not sure how this chart will render, and will send an image if it doesn't look readable.

	2nd requery in place		2nd requery removed
Iteration	1st	2nd	1st	2nd
Start	249	245	249	248
Open rst	242	239	242	242
Open Report	228	225	228	228
Close Report	242	239	242	242
Close rst	248	245	248	248
Open rst	242	239	242	242
Open Report	228	225	228	228
OutputTo	225	222	228	228
Close Report	239	236	242	242
Close rst	245	242	248	248

You can see that under OutPutTo on the second set (requery removed) the connections return to the normal number so that they are at 248 at the beginning of every round.
I do not know why the requery causes OutputTo to reserve connections. But that has been consistently my findings, as it has been with others who have written in. (though there are other causes besides the OutPutTo a PDF.

Having used the tool, I can now clean up the code a lot, and make it work. I am moving the direction of using temporary fields for the varied labels that change depending on data. I can make the fields in the query using the similar coding as was used in the OnOpen event in the report. It is so far opening clean without a requery, which means I will be able do all the printing in one failsafe sweep.

If it works I will come back and show it as solved. If you have other answers, after seeing this data, please let me know.

Oh, the tool comes from here.. http://www.pointltd.com/Downloads/Fi...onnections.zip

Gary

Gary, that is a useful tool.
Well done in finding why it loses connections.
I can't think of anything other than what you have decided to do other than splitting up the records being processed, but then you would have to check that it does actually reset the connections when the vba sub closes after the first run.

It was extremely useful. Thanks!

I have tested it several times after changing my code not to need a requery, and I now have the code basically as:
Open rst, Open Report, Print, OutputTo PDF, Edit record, Close Report, Close rst
It works consistently every time, it doesn't error out at 75 records, and it processes more quickly and somehow sped up the printer.

So thanks for your feedback.
How do I mark this thread as solved?

Gary