Access "dies" printing PDFs

[TerryC]

Dear All,

I have an application that generates individual PDFs (for
each record) from a report. I am using an Access library
purchased from a third party to generate the PDFs using
Acrobat 6.01.

My two problems are "it is slow going" and after about 500-
700 records Access dies and wants to send an error report
to MS. It repairs and compact OK (for another 500 or so
records). The lack of any error code or indication of what
is causing this is frustrating.

I have to generate 10,000 - 30,000 pdfs per month and not
being able to rely on Access to keep running means it can
not be left over night.

I am currenly able to generate about 7 PDFs per minute (in
between crashes). I have posted the code I use below. I am
not an experienced (or very good) coder so I am hopefull
someone can see something stupid I've done that can be
fixt.

==================================================
Dim SavePath As String
SavePath = Forms!Print.Text0
Dim response
With Application.FileSearch
.LookIn = SavePath
.SearchSubFolders = False
.FileName = "*.pdf"
If .Execute() > 0 Then
GoTo cont
Else
MsgBox "There were no files found."
GoTo cont2
End If

cont:

response = MsgBox("There were " & .FoundFiles.Count & _
" file(s) found. Proceed?", 4)
If response = vbYes Then
GoTo cont2
Else: GoTo end_pdf
End If
End With
cont2:
Dim objPDF As New PDFClass
Dim lngResult As Long
Dim oput As String
Dim rpt As String
Dim memb As String
Dim ref As String
Dim strt As Long

Dim conn As ADODB.Connection
Dim rsPDF As New ADODB.Recordset
Dim strCnn As String
Dim RecNo As Long
RecNo = Forms!Print.Text2 'starting record number
strt = Forms!Print.Text2
' Open connection.
Set conn = CurrentProject.Connection
' Open pdf source records
Set rsPDF = New ADODB.Recordset
rsPDF.Open "tax_lhc_members", conn, , , adCmdTable
rsPDF.MoveFirst ' go to first record
rsPDF.Move strt - 1 ' starts from record # on form
print
Do While RecNo - 1 < Forms!Print.Text4 ' loop till end
of file
ref = rsPDF![extra 10]
memb = rsPDF![extra 10] & ".pdf"
oput = SavePath & memb
rpt = "LHCtax_only_pdf"
Const PDFENGINE_PDFWRITER = 1 ' not sure ... does
something?
With objPDF ' creates the pdf
.PDFEngine = PDFENGINE_PDFWRITER
.ReportName = rpt
.ReportWhere = "cstr([extra 10]) = " & ref
.OutputFile = oput
.PrintImage
lngResult = .Result
End With

rsPDF.MoveNext
RecNo = RecNo + 1
Loop
Set objPDF = Nothing
Set rsPDF = Nothing

end_pdf:
=================================================

Any suggestions, ideas or anything will be greatly
appreciated,

Terry

 

[SA]

Terry:

I work with the third party that produced the control you are using.

First, what version of Access are you using? Have you applied all the
service packs appropriate to that version along with the latest Jet Update?

A.) In looking at your code, the first thought (although it probably
doesn't impact it greatly,) is that you are opening a table rather than a
specific recordset that represents the records you want to output (i.e.
those represented by the form.) Relying on a record number to start from in
an open table is not always very reliable (unless the record number is an
auto number field, and even then it is not totally reliable to be set in
sequential fashion for all records, i.e. you can have non-continuous counts
between records.)

As an alternative, is there a SQL string that you can use to select the
records? E.g. a where a flag such as ReportSent = False, (you'd have to
update each record after you create the file and set this field to true), or
where RecordNumber >= X. This would give you a smaller recordset to
navigate in memory.

B.) If you report has images (i.e. JPEGs or Giffs) on it, then most versions
of Access have a memory leak bug that can cause a crash when the report is
run repeatedly. This can be helped by two things 1.) Change the image to a
bit map in a image editing program, or 2.) If you can do that then
substantially reduce the size of the image file on your report.

C.) Relative to Adobe Acrobat 6, it can be quite slow to output PDF files
given the complexity of the report, number of images on a report that it has
to rastorize etc. There may be a number of things that you can do to help
that issue in Acrobat's set up. To manage these, first open the printers
folder and select the Adobe Acrobat printer, right click and select its
printing preferences. Once there, try these things:

1.) Select the "Standard" conversion settings and select Edit; when the
Settings dialog appears, on the compatability setting, try reducing it to
Acrobat 3 or Acrobat 4. This will reduce the complexity of the commands
sent to the Adobe PDF printer.

2.) While you are editing the settings, try reducing the dpi setting to 600
dpi or so (standard printing); uncheck "optimize" for web view if the PDFs
will not be posted on a web server, since this setting causes Acrobat to
produce a larger file with "hints" placed throughout it to allow pages to be
downloaed one at a time on the web. If you use standard fonts in your
report (e.g. Arial and Times New Roman), then you can uncheck on the fonts
page "Embed all fonts".

Once you are done editing the conversion settings Adobe will prompt you to
save the settings asking you to rename the settings to some specific name.

3.) You can also change some basic settings for the Adobe PDF printer, that
may speed things up as well. Once you've save the settings, then click over
to the paper tab. Click the Advanced button. You'll notice that the dpi
there is normally set to 1200 dpi. You might try reducing this to 600
(check your report output, it may need to go back to 1200 for quality); also
under the Post Script options, you'll see that its set to "Optimize for
speed"; you might try setting this to "Optimize for portability" especially
has a lot of images.

D.) On last thing to try; but only after you've applied all service packs
and updates and backed up your db. That is to decompile your database
entirely to clean out any old code that it might contain that could be
causing problems. Here's how:

Before running the following procedure, make sure that you have backed up
your database.

a.) Create a short cut to the MSAccess.exe executable on your deskttop.
b.) Right click on the short cut and bring up its properties.
c.) In the Target box in the short cut, append the full path to your
database in quotations, followed by the /decompile switch

The target string would look something like this:

"%Path To MS Office%\Msaccess.exe" "c:\some directory\some file.mdb"
/decompile

Where %Path To MS Office% is the full path the full path to the MSAccess
executable.

d.) Hold down the shift key and run the short cut.
e.) Hold down the shift key and compact and repair your database.
f.) Open any module and fully compile the database.
g.) Again, hold down the shift key and compact and repair your database.


HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Dear All,

I have an application that generates individual PDFs (for
each record) from a report. I am using an Access library
purchased from a third party to generate the PDFs using
Acrobat 6.01.

My two problems are "it is slow going" and after about 500-
700 records Access dies and wants to send an error report
to MS. It repairs and compact OK (for another 500 or so
records). The lack of any error code or indication of what
is causing this is frustrating.

I have to generate 10,000 - 30,000 pdfs per month and not
being able to rely on Access to keep running means it can
not be left over night.

I am currenly able to generate about 7 PDFs per minute (in
between crashes). I have posted the code I use below. I am
not an experienced (or very good) coder so I am hopefull
someone can see something stupid I've done that can be
fixt.

==================================================
Dim SavePath As String
SavePath = Forms!Print.Text0
Dim response
With Application.FileSearch
.LookIn = SavePath
.SearchSubFolders = False
.FileName = "*.pdf"
If .Execute() > 0 Then
GoTo cont
Else
MsgBox "There were no files found."
GoTo cont2
End If

cont:

response = MsgBox("There were " & .FoundFiles.Count & _
" file(s) found. Proceed?", 4)
If response = vbYes Then
GoTo cont2
Else: GoTo end_pdf
End If
End With
cont2:
Dim objPDF As New PDFClass
Dim lngResult As Long
Dim oput As String
Dim rpt As String
Dim memb As String
Dim ref As String
Dim strt As Long

Dim conn As ADODB.Connection
Dim rsPDF As New ADODB.Recordset
Dim strCnn As String
Dim RecNo As Long
RecNo = Forms!Print.Text2 'starting record number
strt = Forms!Print.Text2
' Open connection.
Set conn = CurrentProject.Connection
' Open pdf source records
Set rsPDF = New ADODB.Recordset
rsPDF.Open "tax_lhc_members", conn, , , adCmdTable
rsPDF.MoveFirst ' go to first record
rsPDF.Move strt - 1 ' starts from record # on form
print
Do While RecNo - 1 < Forms!Print.Text4 ' loop till end
of file
ref = rsPDF![extra 10]
memb = rsPDF![extra 10] & ".pdf"
oput = SavePath & memb
rpt = "LHCtax_only_pdf"
Const PDFENGINE_PDFWRITER = 1 ' not sure ... does
something?
With objPDF ' creates the pdf
.PDFEngine = PDFENGINE_PDFWRITER
.ReportName = rpt
.ReportWhere = "cstr([extra 10]) = " & ref
.OutputFile = oput
.PrintImage
lngResult = .Result
End With

rsPDF.MoveNext
RecNo = RecNo + 1
Loop
Set objPDF = Nothing
Set rsPDF = Nothing

end_pdf:
=================================================

Any suggestions, ideas or anything will be greatly
appreciated,

Terry

 

[Rob]

TerryC,

Can you tell me which 3rd party app you are using to generate a PDF from an
access report?

Thanks.

 

[TerryC]

Steve,

Thanks for your reply and suggestions.

I was trying not to name the control as I had no idea if
that was the cause of my problem or something else.

Versions (I meant to include this with the initial post)

Win2k SP4
Access 2002 (10.6501.6626) SP3
Adobe 6.01

I started out trying to open my recordset from a query but
I kept getting errors (my syntax and lack of experience
being the problem). As I wanted the PDFs created for every
record and was not concerned with the creation order I
ended up using the table.

There are no images in these reports (thank goodness) the
resultent PDFs are only about 10-11k each. Currently set
to Acrobat 5 format.

I will have a play round with the Adobe printing
preferences again, using your suggestions (I'll follow up
with the results).

The reports are purely statements with various fields such
as dates, amount paid / due, address information and such
using 3fonts(2 barcodes). The PDFs are for client
correspondence history.

I am hoping that with persistence this process can be got
to work without crashing and can then be left to run
unattended.

Thanks again Steve for your assistance and detailed
response.

Terry
terry at dee emmm seee dot on dot net
Due to spam the company is phonetic of the above.

-----Original Message-----
Terry:

I work with the third party that produced the control you are using.

First, what version of Access are you using? Have you applied all the
service packs appropriate to that version along with the latest Jet Update?

A.) In looking at your code, the first thought (although it probably
doesn't impact it greatly,) is that you are opening a table rather than a
specific recordset that represents the records you want to output (i.e.
those represented by the form.) Relying on a record number to start from in
an open table is not always very reliable (unless the record number is an
auto number field, and even then it is not totally reliable to be set in
sequential fashion for all records, i.e. you can have non- continuous counts
between records.)

As an alternative, is there a SQL string that you can use to select the
records? E.g. a where a flag such as ReportSent = False, (you'd have to
update each record after you create the file and set this field to true), or
where RecordNumber >= X. This would give you a smaller recordset to
navigate in memory.

B.) If you report has images (i.e. JPEGs or Giffs) on it, then most versions
of Access have a memory leak bug that can cause a crash when the report is
run repeatedly. This can be helped by two things 1.) Change the image to a
bit map in a image editing program, or 2.) If you can do that then
substantially reduce the size of the image file on your report.

C.) Relative to Adobe Acrobat 6, it can be quite slow to output PDF files
given the complexity of the report, number of images on a report that it has
to rastorize etc. There may be a number of things that you can do to help
that issue in Acrobat's set up. To manage these, first open the printers
folder and select the Adobe Acrobat printer, right click and select its
printing preferences. Once there, try these things:

1.) Select the "Standard" conversion settings and select Edit; when the
Settings dialog appears, on the compatability setting, try reducing it to
Acrobat 3 or Acrobat 4. This will reduce the complexity of the commands
sent to the Adobe PDF printer.

2.) While you are editing the settings, try reducing the dpi setting to 600
dpi or so (standard printing); uncheck "optimize" for web view if the PDFs
will not be posted on a web server, since this setting causes Acrobat to
produce a larger file with "hints" placed throughout it to allow pages to be
downloaed one at a time on the web. If you use standard fonts in your
report (e.g. Arial and Times New Roman), then you can uncheck on the fonts
page "Embed all fonts".

Once you are done editing the conversion settings Adobe will prompt you to
save the settings asking you to rename the settings to some specific name.

3.) You can also change some basic settings for the Adobe PDF printer, that
may speed things up as well. Once you've save the settings, then click over
to the paper tab. Click the Advanced button. You'll notice that the dpi
there is normally set to 1200 dpi. You might try reducing this to 600
(check your report output, it may need to go back to 1200 for quality); also
under the Post Script options, you'll see that its set to "Optimize for
speed"; you might try setting this to "Optimize for portability" especially
has a lot of images.

D.) On last thing to try; but only after you've applied all service packs
and updates and backed up your db. That is to decompile your database
entirely to clean out any old code that it might contain that could be
causing problems. Here's how:

Before running the following procedure, make sure that you have backed up
your database.

a.) Create a short cut to the MSAccess.exe executable on your deskttop.
b.) Right click on the short cut and bring up its properties.
c.) In the Target box in the short cut, append the full path to your
database in quotations, followed by the /decompile switch

The target string would look something like this:

"%Path To MS Office%\Msaccess.exe" "c:\some directory\some file.mdb"
/decompile

Where %Path To MS Office% is the full path the full path to the MSAccess
executable.

d.) Hold down the shift key and run the short cut.
e.) Hold down the shift key and compact and repair your database.
f.) Open any module and fully compile the database.
g.) Again, hold down the shift key and compact and repair your database.


HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Dear All,

I have an application that generates individual PDFs (for
each record) from a report. I am using an Access library
purchased from a third party to generate the PDFs using
Acrobat 6.01.

My two problems are "it is slow going" and after about 500-
700 records Access dies and wants to send an error report
to MS. It repairs and compact OK (for another 500 or so
records). The lack of any error code or indication of what
is causing this is frustrating.

I have to generate 10,000 - 30,000 pdfs per month and not
being able to rely on Access to keep running means it can
not be left over night.

I am currenly able to generate about 7 PDFs per minute (in
between crashes). I have posted the code I use below. I am
not an experienced (or very good) coder so I am hopefull
someone can see something stupid I've done that can be
fixt.

==================================================
Dim SavePath As String
SavePath = Forms!Print.Text0
Dim response
With Application.FileSearch
.LookIn = SavePath
.SearchSubFolders = False
.FileName = "*.pdf"
If .Execute() > 0 Then
GoTo cont
Else
MsgBox "There were no files found."
GoTo cont2
End If

cont:

response = MsgBox("There were " & .FoundFiles.Count & _
" file(s) found. Proceed?", 4)
If response = vbYes Then
GoTo cont2
Else: GoTo end_pdf
End If
End With
cont2:
Dim objPDF As New PDFClass
Dim lngResult As Long
Dim oput As String
Dim rpt As String
Dim memb As String
Dim ref As String
Dim strt As Long

Dim conn As ADODB.Connection
Dim rsPDF As New ADODB.Recordset
Dim strCnn As String
Dim RecNo As Long
RecNo = Forms!Print.Text2 'starting record number
strt = Forms!Print.Text2
' Open connection.
Set conn = CurrentProject.Connection
' Open pdf source records
Set rsPDF = New ADODB.Recordset
rsPDF.Open "tax_lhc_members", conn, , , adCmdTable
rsPDF.MoveFirst ' go to first record
rsPDF.Move strt - 1 ' starts from record # on form
print
Do While RecNo - 1 < Forms!Print.Text4 ' loop till end
of file
ref = rsPDF![extra 10]
memb = rsPDF![extra 10] & ".pdf"
oput = SavePath & memb
rpt = "LHCtax_only_pdf"
Const PDFENGINE_PDFWRITER = 1 ' not sure ... does
something?
With objPDF ' creates the pdf
.PDFEngine = PDFENGINE_PDFWRITER
.ReportName = rpt
.ReportWhere = "cstr([extra 10]) = " & ref
.OutputFile = oput
.PrintImage
lngResult = .Result
End With

rsPDF.MoveNext
RecNo = RecNo + 1
Loop
Set objPDF = Nothing
Set rsPDF = Nothing

end_pdf:
=================================================

Any suggestions, ideas or anything will be greatly
appreciated,

Terry

.

 

[TerryC]

Rob,

Thanks for your reply. I was reluctant to name names
without good reason. If you see Steve's response and my
reply you can (I'm sure) work it out.

Thanks again,

Terry

-----Original Message-----
TerryC,

Can you tell me which 3rd party app you are using to generate a PDF from an
access report?

Thanks.

Dear All,

I have an application that generates individual PDFs (for
each record) from a report. I am using an Access library
purchased from a third party to generate the PDFs using
Acrobat 6.01.

My two problems are "it is slow going" and after about 500-
700 records Access dies and wants to send an error report
to MS. It repairs and compact OK (for another 500 or so
records). The lack of any error code or indication of what
is causing this is frustrating.

I have to generate 10,000 - 30,000 pdfs per month and not
being able to rely on Access to keep running means it can
not be left over night.

I am currenly able to generate about 7 PDFs per minute (in
between crashes). I have posted the code I use below. I am
not an experienced (or very good) coder so I am hopefull
someone can see something stupid I've done that can be
fixt.

==================================================
Dim SavePath As String
SavePath = Forms!Print.Text0
Dim response
With Application.FileSearch
.LookIn = SavePath
.SearchSubFolders = False
.FileName = "*.pdf"
If .Execute() > 0 Then
GoTo cont
Else
MsgBox "There were no files found."
GoTo cont2
End If

cont:

response = MsgBox("There were " & .FoundFiles.Count & _
" file(s) found. Proceed?", 4)
If response = vbYes Then
GoTo cont2
Else: GoTo end_pdf
End If
End With
cont2:
Dim objPDF As New PDFClass
Dim lngResult As Long
Dim oput As String
Dim rpt As String
Dim memb As String
Dim ref As String
Dim strt As Long

Dim conn As ADODB.Connection
Dim rsPDF As New ADODB.Recordset
Dim strCnn As String
Dim RecNo As Long
RecNo = Forms!Print.Text2 'starting record number
strt = Forms!Print.Text2
' Open connection.
Set conn = CurrentProject.Connection
' Open pdf source records
Set rsPDF = New ADODB.Recordset
rsPDF.Open "tax_lhc_members", conn, , , adCmdTable
rsPDF.MoveFirst ' go to first record
rsPDF.Move strt - 1 ' starts from record # on form
print
Do While RecNo - 1 < Forms!Print.Text4 ' loop till end
of file
ref = rsPDF![extra 10]
memb = rsPDF![extra 10] & ".pdf"
oput = SavePath & memb
rpt = "LHCtax_only_pdf"
Const PDFENGINE_PDFWRITER = 1 ' not sure ... does
something?
With objPDF ' creates the pdf
.PDFEngine = PDFENGINE_PDFWRITER
.ReportName = rpt
.ReportWhere = "cstr([extra 10]) = " & ref
.OutputFile = oput
.PrintImage
lngResult = .Result
End With

rsPDF.MoveNext
RecNo = RecNo + 1
Loop
Set objPDF = Nothing
Set rsPDF = Nothing

end_pdf:
=================================================

Any suggestions, ideas or anything will be greatly
appreciated,

Terry

.