Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / March 2005

Tip: Looking for answers? Try searching our database.

Access as Automation Server & Printing Reports to PDF files?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Greg Strong - 24 Feb 2005 18:50 GMT
Hello All,

I'm really just looking for ideals on how to use Access as an
automation server for printing of PDF files. I got the ideal from a
previous thread.  See
http://groups-beta.google.com/group/comp.databases.ms-access/browse_frm/thread/6
f8d92950239c4a1/211d9056438b0378?q=group:comp.databases.ms-access+insubject:HOWT
O:+insubject:Automate+insubject:Printing+insubject:of+insubject:Access+insubject
:Reports+insubject:to+insubject:PDF+insubject:files&_done=%2Fgroups%3Fas_q%3D%26
num%3D10%26scoring%3Dr%26hl%3Den%26ie%3DUTF-8%26as_epq%3D%26as_oq%3D%26as_eq%3D%
26as_ugroup%3Dcomp.databases.ms-access%26as_usubject%3DHOWTO:+Automate+Printing+
of+Access+Reports+to+PDF+files%26as_uauthors%3D%26lr%3D%26as_drrb%3Dq%26as_qdr%3
D%26as_mind%3D1%26as_minm%3D1%26as_miny%3D1981%26as_maxd%3D23%26as_maxm%3D2%26as
_maxy%3D2005%26safe%3Doff%26&_doneTitle=Back+to+Search&&d#211d9056438b0378

Also I've found a Microsoft KB article on this subject at
http://support.microsoft.com/kb/q147816/ .

I've already successfully created an application that automatically
creates PDF files for an individual record by looping through a
recordsetclone from a form's recordset. The form requires input into a
text box which provides the criteria to 2 sub-queries that comprise
the query for the form's record source. Thinking out loud what I
would have to do is write some VB script to run from Windows
scheduler. Since I haven't done this before my problems as I see them
now are as follows:

1  Can I pass a variable from the VB script run from Windows scheduler
  to Access?  See below:

  A The purpose of the variable would be the criteria of a query.
    This variable would be a date type. This would provide the
    ability to run the same report over different periods of time on
    one pass. Obviously I would have to incorporate the date into the
    PDF's file name. If no, then I would have to manually change the
    date parameter in the queries, and run over multiple passes. If
    the answer is 'no', see #2 B below.

2  What would be the best way to create the recordset for printing the
  PDF files for the individual records?  See below:

  A If the answer to #1 is yes, I suppose I could write a module
    which opens the form, sets the value of the text box equal to
    variable passed, and calls the command button's on click sub
    procedure.

  B If the answer to #1 is no, then I would need some ideals. The key
    issue here is the record source of the report.  Previously I used
    the same query for the recordset of the form as the query used
    for the report. I simply used the identifying field for each
    record for the where condition on the 'OpenReport' method. I
    suppose I could create copies of the same report and underlying
    query for the record source simply changing the date criteria to
    run the same report over multiple periods.

    1)Can I use the report's underlying query to create a recordset
      to loop through for the individual reports to be saved in PDF
      file format?

     
Hopefully this make sense to you. I am simply tying to see what
options exist from what I already have working. Your ideals would be
appreciated. Thanks!

Signature

Regards,

Greg Strong

jimfortune@compumarc.com - 24 Feb 2005 19:57 GMT
> Hello All,
>
> I'm really just looking for ideals on how to use Access as an
> automation server for printing of PDF files. I got the ideal from a
> previous thread.  See

I still prefer using just Access to create pdf or PostScript reports
directly.  See my comments in:

http://groups-beta.google.com/group/comp.databases.ms-access/msg/3f51d348d2071c60

When you need output that looks like:

http://personalwebs.oakland.edu/~fortune/SampleBillOfLading.pdf

or

http://personalwebs.oakland.edu/~fortune/2005Calendar.zip

Access' built-in reports don't cut it.  It's more work getting the
recordset info into pdf or postscript than setting a Record Source on a
report though.  I also noticed that in the Redmon part of the article
you site that the file name is static.  I think others have done what
you are trying to do so I'll let them help out.

James A. Fortune
Steve - 24 Feb 2005 20:34 GMT
Jim,

Is the Bill Of Lading a preprinted form where you use Access to fill it in?
How did you do it?

Thanks,

Steve

> > Hello All,
> >
[quoted text clipped - 4 lines]
> I still prefer using just Access to create pdf or PostScript reports
> directly.  See my comments in:

http://groups-beta.google.com/group/comp.databases.ms-access/msg/3f51d348d20
71c60

> When you need output that looks like:
>
[quoted text clipped - 11 lines]
>
> James A. Fortune
jimfortune@compumarc.com - 28 Feb 2005 22:57 GMT
It is not a preprinted form.  It was created entirely from Access
without using any pdf printer drivers.  An Access table was used to
store most of the pdf commands for the invariant part of the form.  The
pointing hand and numeral outlines were done using functions.  The
variable part (the data) used the values from the form controls in
Courier font to make it look like someone typed the data onto the form.

Here is the module code for the pointing hand:

Public Type Curve
DX As Double
DY As Double
L1 As Double
Alpha As Double
L4 As Double
Beta As Double
End Type

Public Type PiecewiseCurve
Curves(50) As Curve
End Type

Public Function DrawFilledPiecewiseCurve(dblX As Double, dblY As
Double, dblThickness As Double, thePiecewiseCurve As PiecewiseCurve, N
As Integer) As String
Dim I As Integer
Dim J As Integer
Dim strTemp As String
Dim strCR As String
Dim X2 As String
Dim Y2 As String
Dim X3 As String
Dim Y3 As String
Dim X4 As String
Dim Y4 As String
Dim X As Double
Dim Y As Double
Dim dblSumX As Double
Dim dblSumY As Double
Const DegToRad = 0.0174532925

strCR = Chr(13)
strTemp = "%Pointing Hand" & strCR
strTemp = strTemp & "q" & strCR
strTemp = strTemp & CStr(dblThickness) & " w" & strCR
strTemp = strTemp & CStr(dblX) & " " & CStr(dblY) & " m" & strCR
X = dblX
Y = dblY
For I = 1 To N
 'Convert the curve into pdf commands
 With thePiecewiseCurve.Curves(I)
   'Calculate coordinates of P2 and P3
   X4 = CStr(X + .DX)
   Y4 = CStr(Y + .DY)
   X2 = CStr(Round(X + .L1 * Cos(.Alpha * DegToRad), 6))
   Y2 = CStr(Round(Y + .L1 * Sin(.Alpha * DegToRad), 6))
   X3 = CStr(Round(X4 - .L4 * Cos(.Beta * DegToRad), 6))
   Y3 = CStr(Round(Y4 - .L4 * Sin(.Beta * DegToRad), 6))
   X = X + .DX
   Y = Y + .DY
   strTemp = strTemp & X2 & " " & Y2 & " " & X3 & " " & Y3 & " " &
CStr(X4) & " " & CStr(Y4) & " c" & strCR
 End With
Next I
'try drawing a line to the starting point
strTemp = strTemp & CStr(dblX) & " " & CStr(dblY) & " l" & strCR
'then fill
strTemp = strTemp & "b S" & strCR
strTemp = strTemp & "Q" & strCR
'Debugging Code
'For J = 1 To 15
'  dblSumX = 0
'  dblSumY = 0
'  For I = 1 To J
'    dblSumX = dblSumX + thePiecewiseCurve.Curves(I).DX
'    dblSumY = dblSumY + thePiecewiseCurve.Curves(I).DY
'  Next I
'  strTemp = strTemp & DrawFilledCircle(dblX + dblSumX, dblY + dblSumY,
0.25, 0.93)
'Next J
DrawFilledPiecewiseCurve = strTemp
'MsgBox (Left(strTemp, 100))
End Function

Public Function DrawPointingHand(dblX As Double, dblY As Double, dblH
As Double, dblW As Double) As String
Dim strTemp As String
Dim strCR As String
Dim thePiecewiseCurve As PiecewiseCurve
Dim dblScale As Double

dblScale = 1#
thePiecewiseCurve.Curves(1).Alpha = 30
thePiecewiseCurve.Curves(1).Beta = 10
thePiecewiseCurve.Curves(1).DX = 1# * dblScale
thePiecewiseCurve.Curves(1).DY = 0.108 * dblScale
thePiecewiseCurve.Curves(1).L1 = 0.3 * dblScale
thePiecewiseCurve.Curves(1).L4 = 0.42 * dblScale
thePiecewiseCurve.Curves(2).Alpha = 10
thePiecewiseCurve.Curves(2).Beta = 40
thePiecewiseCurve.Curves(2).DX = 0.4 * dblScale
thePiecewiseCurve.Curves(2).DY = 0.35 * dblScale
thePiecewiseCurve.Curves(2).L1 = 0.17 * dblScale
thePiecewiseCurve.Curves(2).L4 = 0.15 * dblScale
'...
thePiecewiseCurve.Curves(15).Alpha = 120
thePiecewiseCurve.Curves(15).Beta = -160
thePiecewiseCurve.Curves(15).DX = -1.2 * dblScale
thePiecewiseCurve.Curves(15).DY = 0.4 * dblScale
thePiecewiseCurve.Curves(15).L1 = 0.8 * dblScale
thePiecewiseCurve.Curves(15).L4 = 0.9 * dblScale
strTemp = DrawFilledPiecewiseCurve(dblX, dblY, 0.1, thePiecewiseCurve,
15)
DrawPointingHand = strTemp
End Function

James A. Fortune
jimfortune@compumarc.com - 01 Mar 2005 22:30 GMT
> It is not a preprinted form.  It was created entirely from Access
> without using any pdf printer drivers.  An Access table was used to

BTW, did anyone besides pcdatasheet try out:

http://personalwebs.oakland.edu/~fortune/Histogram.zip

?

James A. Fortune
Greg Strong - 24 Feb 2005 21:29 GMT
> I also noticed that in the Redmon part of the article
>you site that the file name is static.  

Are you referring to the code in the text box below?

,----- [ Static output file? ]
| Use this for the "Program Arguments" setting:
| @c:\gs\pdfconf.txt -sOutputFile="C:\temp\output.pdf" -c .setpdfwrite
| -f -
`-----

IIRC this is the code placed in port configuration for the PDF printer set up
with Ghostscript.  This is done because the procedure is automated, and
subsequent commands later copy the file to the correct path with the correct
name. With the suggested configuration %1 you are asked for a file name.

Signature

Regards,

Greg Strong

Greg Strong - 24 Feb 2005 21:41 GMT
>1  Can I pass a variable from the VB script run from Windows scheduler
>   to Access?  See below:

I believe the answer is yes per the MS KB 147816 per
http://support.microsoft.com/kb/q147816/.  See the "Calling Custom Procedures"
section on this page.

Signature

Regards,

Greg Strong

Greg Strong - 25 Feb 2005 17:54 GMT
>>1  Can I pass a variable from the VB script run from Windows scheduler
>>   to Access?  See below:
>
>I believe the answer is yes per the MS KB 147816 per
>http://support.microsoft.com/kb/q147816/.  See the "Calling Custom Procedures"
>section on this page.

This is not necessary. Use 'Scheduled Tasks' in WinXP Pro per
http://support.microsoft.com/default.aspx?scid=kb;en-us;230575 and write the
database to accommodate the functionality of running reports to PDF over
different time periods.

Signature

Regards,

Greg Strong

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.