Batch Printing of Reports to PDF.

  • Thread starter Thread starter RussN
  • Start date Start date
R

RussN

Greetings, group. Thanks in advance for any help you can provide me.

I have an Access97 database that the company currently uses to print
out a variety of forms (reports from Access). The company is moving
some of our operations to a new sofware system, and we would like to
attach these forms to our new software system so they can be printed
out when a workorder from the new system is printed. For this to
happen, the system requires the reports to be in PDF format.
Consequently, I have been tasked with extracting the reports and
saving them in PDF files.

Some background on the database - The database was designed by someone
who knew nothing about database design. The data is stored in a
variety of tables that differ not in structure, but in the 'category'
of data stored in them. The reports that I am printing differ mainly
in data on them, not in design. When I began this conversion process,
I made the decision not to try to redesign the database, because I
felt that it would take too long, and I could print out all the
reports very easily. The tables are in about negative third normal
form, and the reports aren't any better. There are approximately 5000
unique records, each of these requiring 4 (four) reports to be
printed. These reports need to be named with a specific file name (a
part # from the data) to allow easy incorporation into our new system.

Below is the code that I am using to print out the reports. My issue
is that approximately 2 hours, 1000 reports into the operation, the
reports start to print to the wrong file. This happens because the
printer queue slows down, and the file name that I wrote to the ini
file gets changed before the report gets printed, hence the report for
part number 700-xyz gets printed in the file named 701-xyz.

I think that I need to guarantee mutual exclusion to that ini file,
but I don't know how to do it.

If any one understands my problem, and can help me out, I'd greatly
appreciate it.

Russ Newcomer


Below follows the code that I am using:

Code: 
Private Sub PrintAllReports()
Dim strFileName As String, strCriteria As String, strRptName As String
Dim sngTime As Single
Const PDF995 = "C:\pdf995\res\pdf995.ini"
Dim rptTemp As Report
'Declares the variables used in making the reports.
Dim db As Database
Dim ctr As Container
Dim doc As Document
Set db = DBEngine(0)(0)
Set ctr = db.Containers!Reports
'The previous lines declare the database variables to hold the
information needed to retrieve each report.

For Each doc In ctr.Documents
DoCmd.Echo False
'Disables showing of commands

Call DoCmd.OpenReport(doc.Name, acViewPreview)
'Opens the report.

Form.RecordSource = Reports(doc.Name).RecordSource
'Sets the recordset for the form to the same as the report

Call DoCmd.Close(acReport, doc.Name)
'closes the report, because we don't need it anymore.

DoCmd.Echo True
'Re-enables showing of commands

strRptName = doc.Name
'Sets the string equal to the name of the report


Form.Requery
'Re-queries the form to make the report's recordset active on
the form.

Form.Recordset.MoveFirst
'Moves the recordset to the first position

While Not Form.Recordset.EOF  'While we still have a record...
'Set the File Name
strFileName = "c:\ShopForms\" & strRptName & "\" & [Part
#] & ".pdf"
SetIniSetting PDF995, "Parameters", "Output File",
strFileName

'Determine the Criteria, and Print the Report
strCriteria = "[Part #]='" & Me![Part #] & "'"
DoCmd.OpenReport strRptName, acViewNormal, , strCriteria

'Set the Current Time
sngTime = Timer


While ((sngTime + 7 > Timer) Or ((sngTime + 7 - 86400) >
Timer))
'Loop for seven seconds.  I am a crude timer.
'I must be here so that each record will print with the
correct filename
Wend
Form.Recordset.MoveNext  'Move to the next record.
Wend


Next doc

'Clear the variables
Set rptTemp = Nothing
Set ctr = Nothing
Set db = Nothing

Call MsgBox("ALL REPORTS HAVE COMPLETED THE RUN!")
'Show a message box informing the user that their reports have
finished running.

End Sub
 
Russ:

Instead of using solely a timer with a set time, you can try to use the
PDF995's pdfsynch.ini as well. That file has two keys GeneratingPDF which
becomes a 1 when the GhostScript Engine starts and resets to 0 when its done
and PSCreationComplete which becomes 1 when the print spooler is done
running and 0 when the job is first invoked. Obviously takes a lot of
looping and checking. Too, you could check for a file existance and file
lock on the target output file.

All that said, you might just want to look at a different driver. Running
all the processes that PDF995 does leads to a lot of overhead to make it
work correctly. Win2PDF (www.daneprairie.com) and pdfFactory
(www.fineprint.com) are more integrated drivers that are not likely to lead
you to the problem you are encountering; the cost of a good driver will more
than save you the time you are spending on this.

If you want an easy interface to all three drivers, you might look at our
PDF and Mail Library for Access, which supports those aforementioned drivers
as well as the Adobe drivers.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

RussN said:
Greetings, group. Thanks in advance for any help you can provide me.

I have an Access97 database that the company currently uses to print
out a variety of forms (reports from Access). The company is moving
some of our operations to a new sofware system, and we would like to
attach these forms to our new software system so they can be printed
out when a workorder from the new system is printed. For this to
happen, the system requires the reports to be in PDF format.
Consequently, I have been tasked with extracting the reports and
saving them in PDF files.

Some background on the database - The database was designed by someone
who knew nothing about database design. The data is stored in a
variety of tables that differ not in structure, but in the 'category'
of data stored in them. The reports that I am printing differ mainly
in data on them, not in design. When I began this conversion process,
I made the decision not to try to redesign the database, because I
felt that it would take too long, and I could print out all the
reports very easily. The tables are in about negative third normal
form, and the reports aren't any better. There are approximately 5000
unique records, each of these requiring 4 (four) reports to be
printed. These reports need to be named with a specific file name (a
part # from the data) to allow easy incorporation into our new system.

Below is the code that I am using to print out the reports. My issue
is that approximately 2 hours, 1000 reports into the operation, the
reports start to print to the wrong file. This happens because the
printer queue slows down, and the file name that I wrote to the ini
file gets changed before the report gets printed, hence the report for
part number 700-xyz gets printed in the file named 701-xyz.

I think that I need to guarantee mutual exclusion to that ini file,
but I don't know how to do it.

If any one understands my problem, and can help me out, I'd greatly
appreciate it.

Russ Newcomer


Below follows the code that I am using:

Code: 
Private Sub PrintAllReports()
Dim strFileName As String, strCriteria As String, strRptName As String
Dim sngTime As Single
Const PDF995 = "C:\pdf995\res\pdf995.ini"
Dim rptTemp As Report
'Declares the variables used in making the reports.
Dim db As Database
Dim ctr As Container
Dim doc As Document
Set db = DBEngine(0)(0)
Set ctr = db.Containers!Reports
'The previous lines declare the database variables to hold the
information needed to retrieve each report.

For Each doc In ctr.Documents
DoCmd.Echo False
'Disables showing of commands

Call DoCmd.OpenReport(doc.Name, acViewPreview)
'Opens the report.

Form.RecordSource = Reports(doc.Name).RecordSource
'Sets the recordset for the form to the same as the report

Call DoCmd.Close(acReport, doc.Name)
'closes the report, because we don't need it anymore.

DoCmd.Echo True
'Re-enables showing of commands

strRptName = doc.Name
'Sets the string equal to the name of the report


Form.Requery
'Re-queries the form to make the report's recordset active on
the form.

Form.Recordset.MoveFirst
'Moves the recordset to the first position

While Not Form.Recordset.EOF  'While we still have a record...
'Set the File Name
strFileName = "c:\ShopForms\" & strRptName & "\" & [Part
#] & ".pdf"
SetIniSetting PDF995, "Parameters", "Output File",
strFileName

'Determine the Criteria, and Print the Report
strCriteria = "[Part #]='" & Me![Part #] & "'"
DoCmd.OpenReport strRptName, acViewNormal, , strCriteria

'Set the Current Time
sngTime = Timer


While ((sngTime + 7 > Timer) Or ((sngTime + 7 - 86400) >
Timer))
'Loop for seven seconds.  I am a crude timer.
'I must be here so that each record will print with the
correct filename
Wend
Form.Recordset.MoveNext  'Move to the next record.
Wend


Next doc

'Clear the variables
Set rptTemp = Nothing
Set ctr = Nothing
Set db = Nothing

Call MsgBox("ALL REPORTS HAVE COMPLETED THE RUN!")
'Show a message box informing the user that their reports have
finished running.

End Sub
Click to expand...
 
SA - thanks for the help, using the pdfsync.ini file appears to have
solved my problem.

SA said:
Russ:

Instead of using solely a timer with a set time, you can try to use the
PDF995's pdfsynch.ini as well. That file has two keys GeneratingPDF which
becomes a 1 when the GhostScript Engine starts and resets to 0 when its done
and PSCreationComplete which becomes 1 when the print spooler is done
running and 0 when the job is first invoked. Obviously takes a lot of
looping and checking. Too, you could check for a file existance and file
lock on the target output file.

All that said, you might just want to look at a different driver. Running
all the processes that PDF995 does leads to a lot of overhead to make it
work correctly. Win2PDF (www.daneprairie.com) and pdfFactory
(www.fineprint.com) are more integrated drivers that are not likely to lead
you to the problem you are encountering; the cost of a good driver will more
than save you the time you are spending on this.

If you want an easy interface to all three drivers, you might look at our
PDF and Mail Library for Access, which supports those aforementioned drivers
as well as the Adobe drivers.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

RussN said:
Greetings, group. Thanks in advance for any help you can provide me.

I have an Access97 database that the company currently uses to print
out a variety of forms (reports from Access). The company is moving
some of our operations to a new sofware system, and we would like to
attach these forms to our new software system so they can be printed
out when a workorder from the new system is printed. For this to
happen, the system requires the reports to be in PDF format.
Consequently, I have been tasked with extracting the reports and
saving them in PDF files.

Some background on the database - The database was designed by someone
who knew nothing about database design. The data is stored in a
variety of tables that differ not in structure, but in the 'category'
of data stored in them. The reports that I am printing differ mainly
in data on them, not in design. When I began this conversion process,
I made the decision not to try to redesign the database, because I
felt that it would take too long, and I could print out all the
reports very easily. The tables are in about negative third normal
form, and the reports aren't any better. There are approximately 5000
unique records, each of these requiring 4 (four) reports to be
printed. These reports need to be named with a specific file name (a
part # from the data) to allow easy incorporation into our new system.

Below is the code that I am using to print out the reports. My issue
is that approximately 2 hours, 1000 reports into the operation, the
reports start to print to the wrong file. This happens because the
printer queue slows down, and the file name that I wrote to the ini
file gets changed before the report gets printed, hence the report for
part number 700-xyz gets printed in the file named 701-xyz.

I think that I need to guarantee mutual exclusion to that ini file,
but I don't know how to do it.

If any one understands my problem, and can help me out, I'd greatly
appreciate it.

Russ Newcomer


Below follows the code that I am using:

Code: 
Private Sub PrintAllReports()
Dim strFileName As String, strCriteria As String, strRptName As String
Dim sngTime As Single
Const PDF995 = "C:\pdf995\res\pdf995.ini"
Dim rptTemp As Report
'Declares the variables used in making the reports.
Dim db As Database
Dim ctr As Container
Dim doc As Document
Set db = DBEngine(0)(0)
Set ctr = db.Containers!Reports
'The previous lines declare the database variables to hold the
information needed to retrieve each report.

For Each doc In ctr.Documents
DoCmd.Echo False
'Disables showing of commands

Call DoCmd.OpenReport(doc.Name, acViewPreview)
'Opens the report.

Form.RecordSource = Reports(doc.Name).RecordSource
'Sets the recordset for the form to the same as the report

Call DoCmd.Close(acReport, doc.Name)
'closes the report, because we don't need it anymore.

DoCmd.Echo True
'Re-enables showing of commands

strRptName = doc.Name
'Sets the string equal to the name of the report


Form.Requery
'Re-queries the form to make the report's recordset active on
the form.

Form.Recordset.MoveFirst
'Moves the recordset to the first position

While Not Form.Recordset.EOF  'While we still have a record...
'Set the File Name
strFileName = "c:\ShopForms\" & strRptName & "\" & [Part
#] & ".pdf"
SetIniSetting PDF995, "Parameters", "Output File",
strFileName

'Determine the Criteria, and Print the Report
strCriteria = "[Part #]='" & Me![Part #] & "'"
DoCmd.OpenReport strRptName, acViewNormal, , strCriteria

'Set the Current Time
sngTime = Timer


While ((sngTime + 7 > Timer) Or ((sngTime + 7 - 86400) >
Timer))
'Loop for seven seconds.  I am a crude timer.
'I must be here so that each record will print with the
correct filename
Wend
Form.Recordset.MoveNext  'Move to the next record.
Wend


Next doc

'Clear the variables
Set rptTemp = Nothing
Set ctr = Nothing
Set db = Nothing

Call MsgBox("ALL REPORTS HAVE COMPLETED THE RUN!")
'Show a message box informing the user that their reports have
finished running.

End Sub
Click to expand...
Click to expand...
 
Please can someone help.

I'm trying to batch print in access to PDF but don't understand the script programing. Is it possible for some step by step instructions.:confused:

Danny S
 
Back
Top