Printing to PDF from VBA

[notDave]

I am rephrasing my question from last week - I didn't get any replies!

I have a loop that prints about 3000 pdfs in a row. It always ran fine
until recently when I started getting an error (2212 Could not print your
object.) Sometimes I get almost all 3000 printed before that error appears,
sometimes it happens after 50 or so. Anyone have any suggestions as to what
is causing this?

The db is on my desktop, with a single report based on a single table. The
report is sent to the Adobe PDF Writer, again and again. The printer
properties on the PDF Writer are set to output the PDF to a folder on my
desktop as well. So there shouldn't be any issues with network printing etc.
Everything seems to run fine and then pow! just stops dead with that error.
I have to restart the process from where it stopped, sometimes 3 or 4 times.

Access 2003 SP3 on Windows XP Pro SP3, each upgraded with all MS
Windows/Office updates. Adobe Acrobat 7, upgraded to 8, upgraded to 8.14

 

[Jack Leach]

You may be overloading the spooling service. I believe the spooler still
needs to handle each job even though it goes to your desktop rather than an
actual printer.

That would be my only guess.

As an alternative, you can try www.lebans.com/reporttopdf.htm

I'm not positive, but pretty sure that this bypasses spool functions that
are otherwise required by a PDF printer driver (which tend to get testy under
high amounts of pressure (like so many people I know <g> )).

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[notDave]

I have tried splitting the run up into smaller runs, but still get the error
eventually. Also, sometimes it fails after only 10 or 20 have completed, but
I agree... it seems to be something to do with either the network,
permissions, spooling, etc., and not really Access at all.

I have downloaded the mdb from lebans and... it seems too complicated for me
to figure out how to make it do what I need it to do.

My process does a couple of things rather indirectly. It gets a recordset
of all my employees, and starting with the first employee. Then it runs a
report, filtered for the record it is on, and it updates the "caption" of the
report to include the employee number that it's on the moment. Then it
prints that to the default printer, which I have set as the PDF Writer.
Since each "print job" contains a unique caption, the PDF writer can use the
printer queue name as the file name without overwriting the previous report.
After about 45 minutes I get 3000 reports, one for each employee in my table.
That is, until recently.

I don't know how I would use the lebans code to make that happen.

For anyone interested, here is my code:

sql = "SELECT [emp_no] FROM [myTable] GROUP BY [emp_no]"

Set myDb = CurrentDb()
Set myRs = myDb.OpenRecordset(sql)

While Not myRs.EOF

DoCmd.OpenReport "myRpt", acViewPreview, , "[emp_no] = '" & myRs![emp_no] &
"'"
Reports("myRpt").Caption = myRs![emp_no] & "_" & endDate & "_Payroll_Advice"
DoCmd.PrintOut
DoCmd.Close acReport, Reports("myRpt").name, acSaveNo

myRs.MoveNext

Wend


~notDave

 

[Jack Leach]

A few months ago I tried to help someone out who wanted a series of filtered
reports (same report, different ID) from ReportToPDF. The only way I was
able to do this was to use a global variable and refer to it on the Open
event of the report (there's no method for passing an openarg to the report
in Stephen's code). And then the function to generate the series of reports
was a loop that updated the global variable before each report printout. In
any case, it wasn't the most ideal way to handle the situation.

Also, the more I've been considering it, I think ReportToPDF does in fact
require use of the spooler service (I believe the snapshots temp files are
'printed' and then converted). So really, I have no idea if this is what you
need or not.

In fact, I don't even know why you would get this error. The spool is the
first thing that comes to mind, but I'm not very familiar with the inner
workings of the spooling service to try and further track anything down.

Good luck! I have no further 'advice' to offer...

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

I have tried splitting the run up into smaller runs, but still get the error
eventually. Also, sometimes it fails after only 10 or 20 have completed, but
I agree... it seems to be something to do with either the network,
permissions, spooling, etc., and not really Access at all.

I have downloaded the mdb from lebans and... it seems too complicated for me
to figure out how to make it do what I need it to do.

My process does a couple of things rather indirectly. It gets a recordset
of all my employees, and starting with the first employee. Then it runs a
report, filtered for the record it is on, and it updates the "caption" of the
report to include the employee number that it's on the moment. Then it
prints that to the default printer, which I have set as the PDF Writer.
Since each "print job" contains a unique caption, the PDF writer can use the
printer queue name as the file name without overwriting the previous report.
After about 45 minutes I get 3000 reports, one for each employee in my table.
That is, until recently.

I don't know how I would use the lebans code to make that happen.

For anyone interested, here is my code:

sql = "SELECT [emp_no] FROM [myTable] GROUP BY [emp_no]"

Set myDb = CurrentDb()
Set myRs = myDb.OpenRecordset(sql)

While Not myRs.EOF

DoCmd.OpenReport "myRpt", acViewPreview, , "[emp_no] = '" & myRs![emp_no] &
"'"
Reports("myRpt").Caption = myRs![emp_no] & "_" & endDate & "_Payroll_Advice"
DoCmd.PrintOut
DoCmd.Close acReport, Reports("myRpt").name, acSaveNo

myRs.MoveNext

Wend


~notDave

You may be overloading the spooling service. I believe the spooler still
needs to handle each job even though it goes to your desktop rather than an
actual printer.

That would be my only guess.

As an alternative, you can try www.lebans.com/reporttopdf.htm

I'm not positive, but pretty sure that this bypasses spool functions that
are otherwise required by a PDF printer driver (which tend to get testy under
high amounts of pressure (like so many people I know <g> )).

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Dale Fye]

What version of Office are you using? If 2007, you can download and save the
"Save as PDF" add-in from the microsoft site at:
http://www.microsoft.com/downloads/...11-3e7e-4ae6-b059-a2e79ed87041&displaylang=en

With this enabled, you can use the Docmd.OutputTo method to save the report
to your hard drive or network without having to go through the print spooler.