Consulting

Results 1 to 9 of 9

Thread: Solving Error 3048 - Cannot open any more databases

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location

    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

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  3. #3
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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.

  4. #4
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I am concerned that it is crashing on the 75th record.
    What is the error?

  6. #6
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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

  7. #7
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  9. #9
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •