Systemically OutPut Access Report To PDF
Mark C
I have exhaustingly been looking through the newsgroups in search of a way
to systemically output an Access 97 report to a pdf file using the full
version of Adobe Acrobat. I want the user to be able to click a button and
have the Access report saved as a pdf in a directory specified by me the
programmer.
I have seen a posting by John Baker promoting a web-sight that has an mde
that one can reference in any database but after 30 days requires a
registration fee. Someone must have done some automation of this type. Any
help would be much appreciated.
Regards,
Mark C.
Tony Toews
>I have exhaustingly been looking through the newsgroups in search of a way
>to systemically output an Access 97 report to a pdf file using the full
>version of Adobe Acrobat. I want the user to be able to click a button and
>have the Access report saved as a pdf in a directory specified by me the
>programmer.
Creating PDF files from within Microsoft Access
http://www.granite.ab.ca/access/pdffiles.htm
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
dgs5150
Mark - I do have a sample access 97 database where I have put together
and automated the different components necessary to programattically
send an Access 97 report to a PDF file. This does require the full
version of Adobe Acrobat installed as custom because you need the
Acrobat PDFWriter printer that is not installed by default (at least
it was this way in Acrobat 5.0)
Here is an overview of what it does,
1. determine the current default printer
2. change the current printer to Acrobat PDF Writer
3. Modify the registry to save PDF to supplied path and document name
so PDF writer does not prompt for file name and location
4. change back to the default printer
If you are interested I can send you a copy.
Tony Toews
>Mark - I do have a sample access 97 database where I have put together
>and automated the different components necessary to programattically
>send an Access 97 report to a PDF file.
If you like I'd be happy to host that file on my website. Along with credit and your
email address. Or not if you'd prefer.
Mark C
the big bosses at my company and we have decided to go with the
http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm method. I
downloaded and tested the Access 2000 version and it works wonderfully with
no user interaction other then them clicking a button. Sometimes you just
have to admit defeat.
Regards,
Mark C.
here...@yahoo.com
"dgs5150" <dgs...@yahoo.com> wrote in message
news:526ed05a.04071...@posting.google.com...
James Fortune
> I would differently like a copy of that sample database. I have talked to
> the big bosses at my company and we have decided to go with the
> http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm method. I
> downloaded and tested the Access 2000 version and it works wonderfully with
> no user interaction other then them clicking a button. Sometimes you just
> have to admit defeat.
>
> Regards,
> Mark C.
> here...@yahoo.com
I admit defeat on creating standard Access Reports in PDF. Only
Microsoft can make money competing with free ;-). But, for those who
want to think outside the box of Access Report limitations my approach
is new and worthwhile. If you don't use the pdf printer drivers you
can do a lot more with presentation than standard Access Reports can.
I might still do code to create standard Access Reports directly in
pdf just for fun. I like Access Reports, but I'm all for any
techniques that stretch Access' abilities.
James A. Fortune
Taylor Bryant
Maybe it is a moot point at this junction, but to anyone else who is
following this, I have just gone through this struggle. This is a summary of
what probably appears on the above links, but hopefully it helps. This uses
visual basic, so hopefully it is not an inappropriate post, but if anyone is
adventurous...
Two ways- PDFWriter, which in some ways was a bit easier for me. When you
print (i.e. DoCmd.OpenReport [defaults to printing unless you specify
acViewPreview) anything with PDFWriter selected as your default printer, it
first looks in the registry to see if there is a key indicating the name of
the next PDFWriter File to be output. So the trick is to write that
information in the registry beforehand (it is automatically deleted later.)
The problem I encountered with PDFWriter is the way it draws graphics
(non-postscript method, can't remember the name) makes it unacceptable if
the report includes tables or graphs.
So we move to PDF Distiller, which has a different methodology. I created a
new PDFDistiller printer so that I could have the files dropped off in a
general temp directory, which I then snatch the file from, rename it and put
it in an appropriate folder. (i.e. Add a new printer, choose as printer port
a PDF port pointing to c:\TempReports, choose any old printer driver, and
name the printer ProjReportsPDFWriter or ProjReportsDistiller. Then go to
the properties, and under the advanced tab go to drivers, and change it to
either the PDFWriter driver or AdobePS Acrobat Distiller depending on which
method you decide on.)
For both methods in access, I chose to specify an printer specific to the
reports I am printing. This way, I did not need to worry about default
printers or anything of that sort. This is accomplished by opening the
report, clicking "Setup" from the toolbar, which makes a "Page Setup"
dialogue box pop up. Click on the "Page" tab, and where it says "Printer for
YourReportNameHere" click "Use Specific Printer", then click the "Printer"
button to choose specifics.
Damn, I'm long winded.
The trick here is that this essentially creates it's own instance of the
printer that exists only for that report, so any changes you want to make to
the Printing preferences after that point need to be approached through this
dialogue, rather than the usual Printers and Faxes Control Panel access.
(Either that, or make the changes in the Printers and Faxes window, change
it the Report to select a different printer, then change it back to the
original so it loads the newest settings). The PDFWriter preferences are
self explanatory, but for distiller to be automated, you must deselect the
following checkboxes in the Printing Preferences\"Adobe PDF Settings" tab:
Do not send fonts to distiller, View Result in Acrobat, Prompt for the PDF
Filename, Ask to Replace existing PDF File (All unchecked!).
My code follows.
Hopefully this helps.
Taylor
P.S. I am using Acrobat 5.0- I installed 6.0 but too much bloat, had me
running back.
****************************************************************************
PDFWriter method: I kind of jump into the code (skip the declarations), but
hopefully this helps. This is originally for word, and I tried to adapt it
for Access, but I really did not go nuts testing it. I use the Distiller
method.
****************************************************************************
DocSaveName = "C:\Path\PDFNameStringHere" 'This was declared earlier as
a string
' Create Registry Key that tells acrobat the PDF Save name
Call RegistryValue(DocSaveName) 'This function follows, skip to it to
see what happens
DoCmd.OpenReport "2005 Packets Coversheet"
'Note: registry key is automatically deleted after use
'Quit
End Function
'This creates the registry key, needs a reference to (I think...) Windows
Script Host Object Model
Sub RegistryValue(DocSaveName As String)
Dim WshShell As IWshRuntimeLibrary.WshShell
Dim AcroRegVal As String
Dim AcroName As String
AcroName = DocSaveName & ".pdf"
Set WshShell = CreateObject("Wscript.Shell")
AcroRegVal = "HKEY_CURRENT_USER\Software\Adobe\Acrobat
PDFWriter\PDFFileName"
WshShell.RegWrite AcroRegVal, AcroName
' I used the following functions when I was in my test phase to make sure it
was writing to the reg correctly
'strValue = WshShell.RegRead(AcroRegVal)
'MsgBox strValue
End Sub
****************************************************************************
****PDF Distiller method: Much cleaner looking reports, bit more trouble.
Again, I am jumping in...
****************************************************************************
Sub PrintReports(CoordinatorName As String)
'CoordinatorName is criteria I am passing to the report, so it opens only
the
'reports corresponding to a specific coordinator
Dim ReportFormInfo As String 'Title passed from Report to PDF Distiller -
'not necessarily the name of the Report itself,
'but rather the caption field in report properties. '
'It is automatically passed, I only need it in order to rename the PDF
later...
Dim CurPath As String 'Where the database is located now
Dim OldPath As String 'Temporary path where Distiller dumps the PDF
Dim SavePath As String 'Where I want the renamed PDF to end up
OldPath = "C:\Temp\" 'That is where PDF Distiller dumps all my files after
they are made
ReportFormInfo = "2005 Survey Info For Forms.pdf" 'Default name Distiller
saves the Report as
CurPath = Application.CurrentProject.Path & "\" 'Say the db is located on
"C:\ReportDB\"
SavePath = CurPath & CoordinatorName & "\" 'I will ultimately save the new
PDF
'in "C:\ReportDB\John Smith" (the name of the coordinator)
DoCmd.OpenReport "2005 Envelopes Summary Form", , , "[Coordinator] = " &
CoordinatorName 'pass the criteria
Call CreateFolder(SavePath, OldPath, ReportFormInfo)
End Sub
****************************************************************************
This renames the PDF meaningfully, creates folders for it, etc.
****************************************************************************
Sub CreateFolder(SavePath As String, OldPath As String, ReportFormInfo As
String)
Dim fso As IWshRuntimeLibrary.FileSystemObject
Dim OldFile As String
Dim NewFile As String
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(SavePath) Then 'checks to see if
"C:\ReportDB\John Smith" folder exists
fso.CreateFolder (SavePath) 'If not, Creates the folder
"C:\ReportDB\John Smith"
End If
If fso.FileExists(OldPath & ReportFormInfo) Then
'checks for "C:\Temp\2005 Survey Info For Forms.pdf"
fso.CopyFile OldPath & ReportFormInfo, SavePath & ReportFormInfo
'copies it to "C:\ReportDB\John Smith\2005 Survey Info For Forms.pdf"
End If
End Sub
Carol and Doug Hutcheson
I don't know if an earlier response has covered this, but I have for
several years been producing full graphical Access reports by employing
ghostview and gsprint (see http://www.cs.wisc.edu/~ghost/gsview/ for
further info), with the 'printer' set up to save file to disk. By
pre-setting the output filename within your code, you are able to
accomplish the single-click-to-pdf output you have asked for, using
freely available (as in easy to get and costing no money) tools.
It works for me under various flavours of Windows and it has the
advantage of not being tied to Access, so any Windows app printing to
the same 'printer' will produce a pdf.
Just my $0.02.
Cheers,
Doug
kevincar
Hi Mark,
I use Crystal Reports-
I hate it, but I use it... It's Very buggy and it's pretty pricey...
But we had a licensed copy where I work so I just started using that.
Try to think more about what it is you need; If all you need is to
dump an Access report output to a pseudo-printer, there's one called
"Jaws" that costs about $75 bucks.
If you need to create more complicated documents, that link to several
different kinds of databases, maybe Crystal is for you; In my case
I needed to link Access tables to a DataFlex (remember THAT?) system,
and produce PDF files - Crystal works just fine.
As someone else in your thread pointed out, GhostScript would work,
but you would have to code an intermediate step, to make a VB System-call
to a GhostScript command for generating the PDF file from your intermediate
file.... I personally wouldn't do it this way If I had bunches of files
to generate (I generate hundreds a day), or if your app was going to
be run by several different people from different machines, because you
might get into "configuration" issues.
The other solution is to just use Acrobat- A pretty pricey product
also, but the PDF Distiller and Writer are pretty neat.
This Just in:
I found this link:
http://www.acrosoftware.com/Products/CutePDF/writer.asp
It's advertised as "free" - try it and let us know what you think.
Regards,
Kevin