ACC2000: bloat after saving report via code

So I have a routine that makes a PDF for each customer in a loop. It
opens a report in design view, changes the recordset to process only 1
customer, save the report, and makes a PDF from the changed report. I
use this code inside the loop:

----------begin code----------
DoCmd.OpenReport sReportname, acViewDesign ' Must come before "set rpt"
Set rpt = Reports(sReportname)
'rpt.Filter = sWhere ' Access 2000 has a bug here, cannot use .Filter
'rpt.FilterOn = True
rpt.RecordSource = sMySQL
DoCmd.Close , , acSaveYes
DoEvents ' Allow Access to save report.

iRptCount = iRptCount + 1
sPDFName = curdir & sSlsno & sCustnum & ".pdf" ' PDF path and filename.
Call StatusBar("Report " & iRptCount & " of " & cnt & ", Filename=" &
sPDFName) ' DEBUGGING

bSaveDialog = False
bShowPDF = False
blRet = ConvertReportToPDF(sReportname, vbNullString, sPDFName,
bSaveDialog, bShowPDF, 0, "", "", 0, 0) ' Lebans method
----------end code----------

My loop bombs out about record 310. What I found out is, the Access MDB
file slowly increases in size as I go through the loop (I watched it via
Windows Explorer) until it reaches a point where I get an error "Cannot
continue. OpenReport not available at this time."

This error is from the Docmd above when I open the report in design mode
to change it.

If I compact the db via code then I can manually run my loop again, but
it will run from the beginning, not from where the error was.

Another problem is, I cannot select a range of records less than 300 in
number because the customer number is a string, not numeric. (I did not
design the db, a major national company did.)

And if I compact the db every 300 records, it will stop my program, and
till not restart it.

Oh, and I verified that Access 2000 does have a bug whereby the .Filter
property of the report object does not work reliably. So that is out of
the question.

Anyone have any ideas how I can make 700+ pdf files in this loop without
Access bombing out?

Thank you.

I would not implement a solution that relies on changing the design of a form
or report from code. In this case, I would probably create a single record
table to store the filter information. The report's record source could then
be modified to use this table. Then use code in your loop to set the values
of the field(s) in the filter table. You can then open the report without
having to worry about changing the record source property.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


"A Man" wrote:

So I have a routine that makes a PDF for each customer in a loop. It
opens a report in design view, changes the recordset to process only 1
customer, save the report, and makes a PDF from the changed report. I
use this code inside the loop:

----------begin code----------
DoCmd.OpenReport sReportname, acViewDesign ' Must come before "set rpt"
Set rpt = Reports(sReportname)
'rpt.Filter = sWhere ' Access 2000 has a bug here, cannot use .Filter
'rpt.FilterOn = True
rpt.RecordSource = sMySQL
DoCmd.Close , , acSaveYes
DoEvents ' Allow Access to save report.

iRptCount = iRptCount + 1
sPDFName = curdir & sSlsno & sCustnum & ".pdf" ' PDF path and filename.
Call StatusBar("Report " & iRptCount & " of " & cnt & ", Filename=" &
sPDFName) ' DEBUGGING

bSaveDialog = False
bShowPDF = False
blRet = ConvertReportToPDF(sReportname, vbNullString, sPDFName,
bSaveDialog, bShowPDF, 0, "", "", 0, 0) ' Lebans method
----------end code----------

My loop bombs out about record 310. What I found out is, the Access MDB
file slowly increases in size as I go through the loop (I watched it via
Windows Explorer) until it reaches a point where I get an error "Cannot
continue. OpenReport not available at this time."

This error is from the Docmd above when I open the report in design mode
to change it.

If I compact the db via code then I can manually run my loop again, but
it will run from the beginning, not from where the error was.

Another problem is, I cannot select a range of records less than 300 in
number because the customer number is a string, not numeric. (I did not
design the db, a major national company did.)

And if I compact the db every 300 records, it will stop my program, and
till not restart it.

Oh, and I verified that Access 2000 does have a bug whereby the .Filter
property of the report object does not work reliably. So that is out of
the question.

Anyone have any ideas how I can make 700+ pdf files in this loop without
Access bombing out?

Thank you.