CODEWITHSUNDEEP
ms-accessvbams-access-2010
Nick Rossi
Nick Rossi

Reputation: 25

Optimize VBA code that exports to PDF from MS Access

I have two functions that will open and save two diffrent reports based on the same criteria. They are identical except for the refrences:

Function Export_MLR()
On Error GoTo Export_MLR_Err

Dim strReportName As String

DoCmd.OpenReport "Market Rate Notification Final", acViewPreview
strReportName = "S:\National Installations\Market Labor Rates\MLR_INV\MLR\" & Format    (Reports![Market Rate Notification Final].Market_ID, "00") & " " & Replace(Reports![Market  Rate Notification Final].Product_Code, " / ", "_") & "-" & "Market Rate Notification Final" & "_" & Format(Date, "mmddyy") & ".pdf"

DoCmd.OutputTo acOutputReport, "Market Rate Notification Final", "PDFFormat(*.pdf)", strReportName, False, , , acExportQualityScreen
DoCmd.Close acReport, "Market Rate Notification Final", acSaveNo

Export_MLR_Exit:
Exit Function

Export_MLR_Err:
MsgBox Error$
Resume Export_MLR_Exit


End Function

Then I created this function to select the data and put it into the table that the reports refrence line by line:

Function MassMarket()
On Error GoTo MassMarket_ERR

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset


'this query creates my rs1 recordset'
DoCmd.SetWarnings (warningsOff)
DoCmd.OpenQuery "mass_market", acNormal, acEdit
DoCmd.SetWarnings (warningsOn)



Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Mass_market_Rate_change")
Set rs2 = db.OpenRecordset("tbl_Form_Auto")


'this checks and clears any records in rs2' 
If rs2.EOF = False And rs2.BOF = False Then
rs2.MoveFirst
rs2.Delete
End If
rs1.MoveFirst



'loop goes through and adds 1 line runs reports saves them and deletes line'
 Do Until rs1.EOF


    Set rs2 = db.OpenRecordset("tbl_Form_Auto")
        rs2.AddNew
        rs2![MarketID] = rs1![MarketID]
        rs2![Product_ID] = rs1![Product_ID]
       rs2.Update
    Call Export_Invoice
    Call Export_MLR

    rs1.MoveNext
    rs2.MoveFirst
    rs2.Delete

 Loop

MassMarket_Exit:
Exit Function

MassMarket_ERR:
MsgBox Error$
Resume MassMarket_Exit

End Function

Now all of this worked like a charm but it created, on average 16 .pdf files per minute and I had to create 820 .pdf files(about 50 minutes). If this is the best I can do then I will take it but would love to cut this time in half if possible. Thanks for any and all input. NR

Upvotes: 1

Views: 3420

Answers (1)

HansUp
HansUp

Reputation: 97131

In a comment you indicated the bulk of the time is spent in your functions which export the reports to PDF. I'm uncertain whether we can speed those up.

It seems you open a report, reference a value in that report for use as part of the PDF file name, then call the OutputTo method with the same report name to save it as PDF.

In that situation, I'm uncertain what happens "under the hood" ... whether Access opens a second instance of the report object, or is smart enough to see that you already have an instance open and just use that one instead.

As a test, try to signal Access to use the first report instance. From the online help for the ObjectName parameter of the OutputTo method:

If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank.

So what I'm suggesting is try this in your code:

DoCmd.OutputTo acOutputReport, , "PDFFormat(*.pdf)", _
    strReportName, False, , , acExportQualityScreen

If Access complains, try it with an empty string for the ObjectName parameter.

DoCmd.OutputTo acOutputReport, "", "PDFFormat(*.pdf)", _
    strReportName, False, , , acExportQualityScreen

I don't know how much (or even if) this suggestion will speed up your code. But if you can't speed up those export functions somehow, my hunch is your hope to cut the overall time by half is a shaky proposition.

Upvotes: 1

Related Questions