Out of memory generating reports in batch

[Cris]

I have a memory problem generating reports in Access.

I have a functionnality which it is going to be run in daily basis by the user to generate the reports for records created on that day automatically and export them to pdf files.

This works fine but on a day can be until 250 reports to generate and the computing it is going out of memory.

I've been looking for the way to optimize this and what i found it is that when i closed the report after exporting to pdf the memory it is not freed and that's why after generating x number or records there is not memory anymore.

I don't know why report it is not freeing memory when i close it or where i can found information of how Access manage memory. Does anybody can direct me where to look for the right information to solve my problem?

Thanks in advance for your help

 

[vbaInet]

How do you generate the 250 reports?

 

[Cris]

I run a query to determine what are the records to generate.

Then, for each record to print
- I open the report with : Docmd.OpenReport [ReportName],acViewPreview

- I export it to PDF: Docmd.Output To acOutputReport [reportname], PDF Format (*.pdf), [FileName]

- I close the report : Docmd.Close acReport, [reportName]

I would expect that closing the Report free up memory. However, i see memory usage for MSACCESS process decrease but not coming back to the state before opening the report, so with each iteration the memory used by MSACCESS it's increasing so i run out of memory.

Thanks for your reply

 

[boblarson]

I run a query to determine what are the records to generate.

Then, for each record to print
- I open the report with : Docmd.OpenReport [ReportName],acViewPreview

- I export it to PDF: Docmd.Output To acOutputReport [reportname], PDF Format (*.pdf), [FileName]

- I close the report : Docmd.Close acReport, [reportName]

I would expect that closing the Report free up memory. However, i see memory usage for MSACCESS process decrease but not coming back to the state before opening the report, so with each iteration the memory used by MSACCESS it's increasing so i run out of memory.

Thanks for your reply

Sorry, but that isn't the full code which is doing it. Post the code not this abbreviated stuff. If we can't see the whole code it is like telling a doctor that "Doc, my arm is killing me" and showing him only your fingers.

 

[Cris]

This is the code that generates the reports, i hope this is more helpful. I haven't write it all because i have the same problem even if i just call the report outside of this function. I mean, just the fact of opening the report and closing it it is making the memory used by Access increase. But maybe you cant find something i can improve here. Thanks

Set qdf = CurrentDb.QueryDefs("rql_Rapports_MCT")
qdf![parameterContrat] = lstContrats.ItemData(varItem)
Set rst = qdf.OpenRecordset

If Not rst.EOF Then
If SysCmd(acSysCmdGetObjectState, acForm, "fbr_msg_zipmct") = 0 Then
DoCmd.OpenForm "fbr_msg_zipmct"
DoEvents
End If
Do While Not rst.EOF

txtNoFormulaire = Trim(Mid(StringFromGUID(rst!NoFormulaire), 6, 39))

DoCmd.Echo False
DoCmd.OpenReport "rpt_detaille", acViewPreview, , , , "pdf"
name = Projet.NoProjetMct & "-" & [Reports]![rpt_detaille]!NoContrat.Column(2) & "-" & [Reports]![rpt_detaille]!NoContrat.Column(5) & "-RJ-" & rst!NumeroFormulaire & "-" & [Reports]![rpt_detaille]![sous_rpt_Cartouche_RJ]!txtrevision & "-HQ"
'REPORT pdf
DoCmd.OutputTo acOutputReport, "rpt_detaille", "PDF Format (*.pdf)", contratFolder & "\" & name & ".pdf"
DoCmd.Close acReport, "rpt_detaille"
DoCmd.Echo True
Me.Form.SetFocus
DoEvents
rst.MoveNext
Loop
End if

 

[DCrake]

Why are you previewing the reports each loop? Can you not simply output them to PDF this will cut out alot of cashing.

What version of Acces are you using?

 

[Cris]

That is a good point to save memory, but i don't know and i didn't find the way to tell the report what record do i want to print. At least, i couldn't see a parameter in the outputTo function to send it a query.

I am using Access 2007 SP2. That's why I am able to use the OutputTo function now that it is available in this version.

Thanks

 

[vbaInet]

Code tidied up a wee bit:

Set qdf = CurrentDb.QueryDefs("rql_Rapports_MCT")
qdf![parameterContrat] = lstContrats.ItemData(varItem)
Set rst = qdf.OpenRecordset

If Not rst.EOF Then
    If SysCmd(acSysCmdGetObjectState, acForm, "fbr_msg_zipmct") = 0 Then
[COLOR=Red][B]        DoCmd.OpenForm "fbr_msg_zipmct"[/B][/COLOR]
    End If
    
    DoCmd.Echo True
    
    Do While Not rst.EOF
[B][COLOR=Red]        txtNoFormulaire = Trim(Mid(StringFromGUID(rst!NoFormulaire), 6, 39))[/COLOR][/B]
        
        DoCmd.OpenReport "rpt_detaille", acViewPreview, , , , [B][COLOR=Red]"pdf"[/COLOR][/B]
[COLOR=Red][B]        Name[/B][/COLOR] = Projet.NoProjetMct & "-" & [Reports]![rpt_detaille]!NoContrat.Column(2) & "-" & [Reports]![rpt_detaille]!NoContrat.Column(5) & "-RJ-" & rst!NumeroFormulaire & "-" & [Reports]![rpt_detaille]![sous_rpt_Cartouche_RJ]!txtrevision & "-HQ"
        
        'REPORT pdf
        DoCmd.OutputTo acOutputReport, "rpt_detaille", "PDF Format (*.pdf)", contratFolder & "\" & Name & ".pdf"
        DoCmd.Close acReport, "rpt_detaille"
[B][COLOR=Red]        Me.Form.SetFocus[/COLOR][/B]
        DoEvents
        rst.MoveNext
    Loop
    
    DoCmd.Echo False
End If

All my queries in red.
1. Why are you opening a form?
2. txtNoFormulaire is not in use in your code
3. Why are you sending "pdf" in OpenArgs? You must be using it for something in your report
4. Don't use Name, it's a function for renaming files. So change that variable name to something else
5. Why are you trying to set focus back to the form?

 

[Cris]

Code tidied up a wee bit:

Set qdf = CurrentDb.QueryDefs("rql_Rapports_MCT")
qdf![parameterContrat] = lstContrats.ItemData(varItem)
Set rst = qdf.OpenRecordset
 
If Not rst.EOF Then
    If SysCmd(acSysCmdGetObjectState, acForm, "fbr_msg_zipmct") = 0 Then
[COLOR=red][B]     DoCmd.OpenForm "fbr_msg_zipmct"[/B][/COLOR]
    End If
 
    DoCmd.Echo True
 
    Do While Not rst.EOF
[B][COLOR=red]     txtNoFormulaire = Trim(Mid(StringFromGUID(rst!NoFormulaire), 6, 39))[/COLOR][/B]
 
        DoCmd.OpenReport "rpt_detaille", acViewPreview, , , , [B][COLOR=red]"pdf"[/COLOR][/B]
[COLOR=red][B]     Name[/B][/COLOR] = Projet.NoProjetMct & "-" & [Reports]![rpt_detaille]!NoContrat.Column(2) & "-" & [Reports]![rpt_detaille]!NoContrat.Column(5) & "-RJ-" & rst!NumeroFormulaire & "-" & [Reports]![rpt_detaille]![sous_rpt_Cartouche_RJ]!txtrevision & "-HQ"
 
        'REPORT pdf
        DoCmd.OutputTo acOutputReport, "rpt_detaille", "PDF Format (*.pdf)", contratFolder & "\" & Name & ".pdf"
        DoCmd.Close acReport, "rpt_detaille"
[B][COLOR=red]     Me.Form.SetFocus[/COLOR][/B]
        DoEvents
        rst.MoveNext
    Loop
 
    DoCmd.Echo False
End If

All my queries in red.
1. Why are you opening a form?
2. txtNoFormulaire is not in use in your code
3. Why are you sending "pdf" in OpenArgs? You must be using it for something in your report
4. Don't use Name, it's a function for renaming files. So change that variable name to something else
5. Why are you trying to set focus back to the form?

VbaInet, Here are the answers:

1. I am opening a form to indicate the user to wait until the exportation finish. It is just a form with a label showed during the process
2. txtNoFormulaire is a textbox in my form which value is used as a parameter to the report query. This parameter indicates what record it is going to be printed.
3. Yes, "pdf" it is used in the report. I used this report in other place where the user can generate report based on different criteria. This parameter indicates the report should use the query for print just one record.
4. Thanks for the advice, I am going to change the variable "name"
5. I set focus to the form because i had a problem when i was generating the reports. While I generate the reports, if the user clicks outside the Access Window, Access give me an error. So, with the setfocus on the form i prevent this problem. I don't know if I am clear here i don't remember the error message, maybe i should have to find it to clarify this.

 

[vbaInet]

5. I set focus to the form because i had a problem when i was generating the reports. While I generate the reports, if the user clicks outside the Access Window, Access give me an error. So, with the setfocus on the form i prevent this problem. I don't know if I am clear here i don't remember the error message, maybe i should have to find it to clarify this.

Set the Popup and Modal properties of the form to ensure that the focus stays on the form until its closed.

Open the report in hidden mode, acHidden, which is one of the arguments in DoCmd.OpenReport

 

[Cris]

Setting my report acHidden don't save memory. In fact, the report wasn't visible. I wonder if this is the way Access work that closing the report doesn't free memory. I don't have idea how Access manage memory. I've been searching but i can't find a reference that explain this behavior to know where to look for fix this problem.

do you have another suggestion?

 

[NhocCuteGirl]

How do you generate the 250 reports?
__________________

 

[vbaInet]

Setting my report acHidden don't save memory. In fact, the report wasn't visible. I wonder if this is the way Access work that closing the report doesn't free memory. I don't have idea how Access manage memory. I've been searching but i can't find a reference that explain this behavior to know where to look for fix this problem.

do you have another suggestion?

I just said that as a follow-up to my previous statement.

1. Remove DoCmd.Echo - the form is sufficient
2. Replace txtFormulaire with a String variable and before the MoveNext line flush the variable:

VariableName = vbNullString
3. You seem to be outputting the same report but with different names. Run the OutPutTo code once, make a copy of the report and rename it. Repeat the "make copy, rename" action for as many times as you wish.

 

[Cris]

I just said that as a follow-up to my previous statement.

1. Remove DoCmd.Echo - the form is sufficient
2. Replace txtFormulaire with a String variable and before the MoveNext line flush the variable:

VariableName = vbNullString
3. You seem to be outputting the same report but with different names. Run the OutPutTo code once, make a copy of the report and rename it. Repeat the "make copy, rename" action for as many times as you wish.

vbaInet:

About changing txtFormulaire, this is a parameter for my report query. It doesn't work if I replace it by a string variable as you suggest me.

The report it is not the same every time. Every pass on the loop generates a different report. I can just make a copy and rename. I really need to generate each report and this is the point where my memory increase and didn't decrease when i close the report.


NhocCuteGirl:

My code is in the posts, let me know if you need other information.

Thanks

 

[vbaInet]

Some more changes to your code.

Drop this in a Module

public strFormulaire as string

public function GetFomulaire() As String
    GetFormulaire = strFormulaire
end function

Set qdf = CurrentDb.QueryDefs("rql_Rapports_MCT")
qdf![parameterContrat] = lstContrats.ItemData(varItem)
Set rst = qdf.OpenRecordset

If Not rst.EOF Then
    If SysCmd(acSysCmdGetObjectState, acForm, "fbr_msg_zipmct") = 0 Then
        DoCmd.OpenForm "fbr_msg_zipmct"
    End If
    
    Do While Not rst.EOF
        strFormulaire = Trim(Mid(StringFromGUID(rst!NoFormulaire), 6, 39))
        
        DoCmd.OpenReport "rpt_detaille", acViewPreview, , , acHidden, "pdf"
        With Reports![rpt_detaille]
            varName = Projet.NoProjetMct & "-" & !NoContrat.Column(2) & "-" & !NoContrat.Column(5) & "-RJ-" & rst!NumeroFormulaire & "-" & ![sous_rpt_Cartouche_RJ]!txtrevision & "-HQ"
        End With
        DoCmd.Close acReport, "rpt_detaille", acSaveNo

        DoCmd.OutputTo acOutputReport, "rpt_detaille", "PDF Format (*.pdf)", contratFolder & "\" & varName & ".pdf"
        DoEvents

        strFormuLaire = vbNullString
        rst.MoveNext
    Loop

    set rst = nothing
End If

In the query condition put this instead:

GetFomulaire()

 

[Cris]

Some more changes to your code.

Drop this in a Module

public strFormulaire as string
 
public function GetFomulaire() As String
    GetFormulaire = strFormulaire
end function

Set qdf = CurrentDb.QueryDefs("rql_Rapports_MCT") 
qdf![parameterContrat] = lstContrats.ItemData(varItem)
Set rst = qdf.OpenRecordset
 
If Not rst.EOF Then
    If SysCmd(acSysCmdGetObjectState, acForm, "fbr_msg_zipmct") = 0 Then
        DoCmd.OpenForm "fbr_msg_zipmct"
    End If
 
    Do While Not rst.EOF
        strFormulaire = Trim(Mid(StringFromGUID(rst!NoFormulaire), 6, 39))
 
        DoCmd.OpenReport "rpt_detaille", acViewPreview, , , acHidden, "pdf"
        With Reports![rpt_detaille]
            varName = Projet.NoProjetMct & "-" & !NoContrat.Column(2) & "-" & !NoContrat.Column(5) & "-RJ-" & rst!NumeroFormulaire & "-" & ![sous_rpt_Cartouche_RJ]!txtrevision & "-HQ"
        End With
        DoCmd.Close acReport, "rpt_detaille", acSaveNo
 
        DoCmd.OutputTo acOutputReport, "rpt_detaille", "PDF Format (*.pdf)", contratFolder & "\" & varName & ".pdf"
        DoEvents
 
        strFormuLaire = vbNullString
        rst.MoveNext
    Loop
 
    set rst = nothing
End If

In the query condition put this instead:

GetFomulaire()

vbaInet:

I had to leave this problem for a while but finally i get to make the changes you send mi last time. However i couldn't make it work, closing the report before th OutputTo is not working. I suppose the report is not
saving the query because is asking for parameter values.

I tried something that I found interesting to avoid open the report. I change the QueryDefinitios in code:

 Set qd = CurrentDb.QueryDefs("rql_Rapport_pdf")
qd.SQL = "SELECT t1.*, CStr(t1.NoFormulaire) AS strNoFormulaire, t3.NumeroFormulaire AS NumeroUnique, " & _
" t2.Nocontrat, t2.Rapport FROM " & _
"(tbl_Rapport_Journalier AS t1 INNER JOIN " & _
" tbl_Rapport AS t2 ON t1.NoRapport = t2.NoRapport) " & _
" LEFT JOIN tbl_Numero_Unique AS t3 ON t1.NoFormulaire = t3.NoFormulaire " & _
" WHERE t1.NoFormulaire = " & strMCTFormulaire
                                                                                                   DoCmd.OutputTo acOutputReport, "rpt_detaille", "PDF Format (*.pdf)", contratFolder & "\" & varName & ".pdf"
DoEvents

To do this i had to set the Report recordsource to "rql_Rapport_PDF".

This saves memory because i don't have to open the report but I am still having problems just printing. Just the fact of printing the report is consuming memory and when there is not anymore my application just freeze or closes Access.

I think what I really need it is a way to free up memory when generating each report what Access is not doing.

Any ideas?

Thanks

 

[vbaInet]

Reports don't update records. What parameter values was it asking for?

I'm beginning to suspect the printing device is interferring with this routine. It might be that the printer's spooling facility is eating up the memory and Access hangs when it can't get any memory to perform its function.

 

[Cris]

Reports don't update records. What parameter values was it asking for?

I'm beginning to suspect the printing device is interferring with this routine. It might be that the printer's spooling facility is eating up the memory and Access hangs when it can't get any memory to perform its function.

I didn't mean the report was trying to update the record. The value is asking for is used on the report. Since I set the recordsource in the Report_Open event, I think the report doesn't find this value because the report is already closed whent the OutputTo action is executed.

Anyways, this wouldn't be a problem anymore if i change the query definition in code.

I am not sure what is the "printer's spooling facility" but is right that my memory is all used and theres is not anymore left to continue generating reports. But I test the report just previewing it and it has the same behavior, It opens, uses memory and when i closed it doesn't free all the memory used.

So, What I would like to know is if Access doesn't release the memory used by objects on the report immediatly, I don't have an idea if Access manage memory Or maybe it's my report who has this problem.

Do you have other ideas where could i look, I've been searching on the internet but i can't find a similar problem.

 

[vbaInet]

Why are you setting the report's RecordSource in the Open event of the report? You can use the WHERE argument of the OpenReport method to filter records if you need to.

What happens when you comment out the OutputTo?

Let's see your db.

 

[Cris]

I set the recordsource in the Open event because I use this report with two differente queries depends on user request.

I made this test changing the Query definitions so I don't have to open the report, just use the OutputTo so I am able to generate more reports but still memory is not released so my limitation is the quantity of memory available on the user computer. This could be already a good improvement but when computer runs out of memory the OutputTo doesn't send an error it just freeze the application so it wouldn't be nice to the user.

I comment the OutputTo and the memory used is less but still continue to increment depends on the number of reports to generate without freeing it up.

Sorry, what did you mean by see the db? Do you mean attached?