Solved

Automatic (VBA) creation of Adobe PDF (v.6) from MS Access (2003) report.

Posted on 2004-08-02
16
924 Views
Last Modified: 2013-12-03
I have pursued and tried to understand the various “fragmented” solutions, advice and work a-rounds currently on file, but none provide a clear “Step by Step” coding solution for my problem which is:

A solution using MS Access (2003) Forms, Reports and VBA code etc. that will enable a User to open a "Print report" form dialog box with a “Print, save and email” option that will Automatically, background print the selected report directly to Adobe Acrobat Writer .pdf (V.6) and which will automatically (without any further user input) create (if new) and or save the pdf document using information (Drive Name, Path, Folder Name and  File Name) pulled from a  reference table with the current MS access database.

0
Comment
Question by:7dud
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 11691504
Try my example on
www.almosawe.ae/jaffer/pdf-Report.zip
Which includes everything you will require.

jaffer
0
 

Author Comment

by:7dud
ID: 11691711
Thanks for the quick response.

Is there any way to do it without using "Ghostscript" and "Makepdf"? I would prefer to solve the problem using only MS Access 2003 and Adobe Acrobat Reader V6.

Also the finished product will be part of a MS app that we will be run on 10-20 networked machines and the extra time and administration overhead to install "Ghostscript" and "Makepdf" etc. on all computers, isn't desirable.

Jeromy
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11691752
Jeromy

From what I read, there is no direct way with Adobe Acrobat Reader, thats when I found this solution and modified it and made to the public.

I think the only way to do with Adobe Acrobat Reader, is, when the save as dialog popup, you can use Sendkey commands to fill the dialogs automatically, but it is not adviseble, especially with muliti users.

You still have to install Adobe Acrobat Reader on every PC, Right?
You don't need Makepdf, its just an interface if you want to use Ghostscript directly, so you only need to install Ghostscript.

Besides, try to put Ghostscript on the server and point to it in the code, as the code needs the exact path of Ghostscript anyhow.

jaffer
0
 

Author Comment

by:7dud
ID: 11691996
Dear Jaffer,

Thanks once again for the quick reply. Your suggestions are well taken and failing the availability of any other solution, I will probably implement your solution.

Just as a side note I mis-typed when I said "Adobe Acrobat Reader" I meant the full version of "Adobe Acrobat Writer" (and distiller if needed)

If I have the AA Writer v6 SDK, would that help?

Being inherently lazy, I’m trying to cut out as much configuration/Administrator work as possible.

Cheers
Jeromy (A Kiwi, living in Thailand)
0
 
LVL 2

Expert Comment

by:sigs66
ID: 11692047
Hi

I had a similar problem and my work around involved a piece of software called pdf995. I was able to output the access report to pdf reliably and the pdf995 printer can be set up as a network printer.

I know it is not want you want but it gets aroud all the problems you mentioned

Cheers Steve
A kiwi living in NZ
0
 

Author Comment

by:7dud
ID: 11700148
Steve,

1. Can you provide implimentation details?
2. Location of the "pdf995" site.
3. Any problems encountered?
4. Have you looked at Jaffers solution?

Jeromy
(Born in Ashburton, schooled in Christchurch, in Thailand since late 70's)

0
 
LVL 2

Expert Comment

by:sigs66
ID: 11700666
Implementation is pretty straight forward go and look at www.pdf995.com is the site and they have a free version

try it see how it goes - you may need to check their developers FAQ

The one problem I encountered was that my code was faster than the printer - so I had program a delay into the code.

I have not looked at jaffer's solution

Cheers
Steve
0
 
LVL 1

Expert Comment

by:fcomte
ID: 11715415
Hi,
Just want to warn you : dont use pdf995 on a terminal server (win2000 TS). Even if the company says it works fine, there are a lot of problems.
Fred
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:7dud
ID: 11722654
Dear Fred,

Thanks for the warning, advice well taken.

Jeromy
0
 

Author Comment

by:7dud
ID: 11760513
Dear Jaffer,

Have been able to get your solution working OK. Is there any way to save the resultant .pdf in colour instead of B&W? I checked the postscript settings and colour isn't an option.

Jeromy
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 11761282
Jeromy

I am glad its working for you,
Color and Resolution (dpi) depends on the postscript printer you choose,
so, Yes color is an option.

jaffer
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 12345652
You may wish to study FreePDF:

  http://freepdfxp.de/fpxp.htm

It is totally free and uses Ghostscript and the Apple Postscript driver which is capable of handling colour.
Be sure to download the "Administration Manual" as well as the sources (some basic code samples) and perhaps even the multidocument option.

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 12635322
Did this work for you?

/gustav
0
 

Author Comment

by:7dud
ID: 12640779
Solution from Jaffer is working OK, so am sticking with that. Consequently didn't really check yours out.
Still would prefer a direct native adobe/Access solution, but "You can't always get what you want, but I try"
7DUD
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12641937
Hi 7DUD

Could you please press on accept button next to the answer that solved your problem and close the question,

thanks,

jaffer
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 12642368
Well, 7DUD (not 7 dudes I hope!), if you find the ideal solution, feel free to post back here so we all learn.
If Jaffer's solution works for you - be it permanently or as a work-around - you should accept his answer.

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I. Introduction In a previous article (http://www.experts-exchange.com/Web_Development/Document_Imaging/A_6537-PaperPort-Upgrade-How-to-download-and-install-updated-versions-of-PaperPort-11-and-12.html) (now deprecated), I discussed how to upgrad…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Microsoft Office Picture Manager is not included in Office 2013. This comes as quite a surprise to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This video expla…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now