Solved

Create a PDF from a report without prompts - Access 2003

Posted on 2013-10-23
27
514 Views
Last Modified: 2013-10-27
I am trying to create a PDF from an Access report, but it is part of a loop, so I do not want the PDF writer to open and nor do I want to be prompted for a file name. The loop runs a report, with a counter, so the file it creates will be for example, "Invoice 1.pdf", then "Invoice2.pdf, etc. I have seen various solutions here, but they seem to not do quite what I want.
0
Comment
Question by:rick_danger
  • 9
  • 8
  • 8
  • +1
27 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39595815
Something like this?

Dim i as integer
For i = 1 to 5
          DoCmd.OutputTo acOutputReport, "YourReportName", acFormatPDF, "Invoice" & i & ".pdf", False
next

Open in new window

0
 
LVL 75
ID: 39595824
acFormatPDF only started in A2007 or A2010 I think.

I know how to do this ... because I had to do it at work. I will post later tonight. In fact, I have a PDF (no pun) with the exact instructions on how to set this up - step by step - at work - and can post that tomorrow.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39595828
Right you are.  I overlooked the A2K3 part.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 39596295
rick_danger,

I use the Lebans code to convert to PDF in Access 2003.
http://www.lebans.com/reporttopdf.htm

Attached is a sample I made a while ago.
(It loops the customers instead of using a counter)
judging form your post, you seem like you can see what I did here and adapt it to your application.

JeffCoachman
Access-BulkConvertIndividualCust.mdb
0
 
LVL 75
ID: 39596324
Just an FYI ... not positive, but should you  happen to upgrade to >=A2007, I think the Leban's PDF has some issues. But for A2003, the Leban's code certainly does work ... and I have used it.

Also ... I just realized that my approach - which was my only option for running MDBs in A2010 environment ... and involves creating a 2nd virtual PDF Printer on your system (not hard to do) ... will require the same thing on any system that needs to run your db. In my case, I had to set up about a dozen peeps systems. Again, my only option.

mx
0
 

Author Comment

by:rick_danger
ID: 39597519
Boag2000
I have tried this on my PC, which Access 2010, but it doesn't work. Is this the problem that you mentioned? I will be trying it out on the A2003 machine later. The solution looks really good, and I appreciate the way you have included the loop too.

I'll let you know how I get on.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39597755
<but it doesn't work>
Please clearly define "doesn't work"

<Is this the problem that you mentioned?>
I never mentioned a "problem" per se, ...I only stated that instead of a counter, I was looping the Customer table.

If you read the info in the Lebans.com link, and read my notes in the code for the button, you will see that you need to add the dynapdf.dll to your windows system32 folder in order for the code to work properly.

Notes:
You originally stated that you were using Access 2003:
<Create a PDF from a report without prompts - Access 2003>
...but now you say you are using Access 2010:
<I have tried this on my PC, which Access 2010,>
Then code contain aspects specific to Access 2003 (like turning the screen off, because the "Open Hidden" option was not available for Reports in Access 2003)
So in this case it is more of the "Format" of Access you are using, not really the version.
So My presumption is that you have at least one user still running Access 2003, so there is no need to modify the code.
But to be clear,  the code will run fine, unmodified, in most versions of Access.
Just an FYI

;-)

JeffCoachman
0
 

Author Comment

by:rick_danger
ID: 39597825
Jeff
It wasn't a criticism, just an observation. I appreciate that I had asked for an A2003 solution, but I've not been able to try that yet. That was all I had to test it on immediately.

I'll be able to test it tomorrow.

Thanks again
0
 
LVL 75
ID: 39597981
rick_danger
Do you want me to post a procedure for setting this up - noting what I said in my FYI post above ?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39597999
<It wasn't a criticism, >
I did not take it as criticism...
;-)

I just wanted you to know that the sample was specific to 2003, and that you needed to have the dll in a specific location.

I just want to make sure that the sample works OK for you first, ...then it can probably be adapted for your situation (using the counter)

So keep us posted.
;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39598032
<Do you want me to post a procedure for setting this up - noting what I said in my FYI post above ? >

Yes, Joe, ...
The more samples the better...
;-)
Always bet to have multiple alternatives...
;-)

Jeff
0
 
LVL 75
ID: 39598052
I will wait to hear from Rick - again after he reads my FYI post.
Leban's should work. However, "I have tried this on my PC, which Access 2010, " ... and that my be his issue. Again, seems I recall an issue running Leban's with and MDB in A2010 environment.
0
 

Author Comment

by:rick_danger
ID: 39598678
This is A2010 - When I run it, it seems not to produce any output. When I go step-by-step, it goes to these lines:
s = "Sorry...cannot find the DynaPDF.dll file"
s = s & "Please copy the DynaPDF.dll file into the same folder as this Access MDB or your Windows System32 folder."
s = s & "Please copy the StrStorage.dll file into the same folder as this Access MDB or your Windows System32 folder."

Both files reside in the same folder as the Access database.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39598801
<Both files reside in the same folder as the Access database. >
These files really need to live in the system 32 folder, else for each db that need this functionality you need to add the dll to each folder each db file lives in...

I put both files in System 32 folder and this works fine in Access 2003, 2007, 2010 and 2013...
0
 
LVL 75
ID: 39598820
Don't you have to set a Reference to the DLL ?
0
 

Author Comment

by:rick_danger
ID: 39598927
Tried it both ways, still same problem. It doesn't produce a PDF. If I have to set a Reference, how do I do it. If i go to Tools / References and Browse the 2 dlls do not appear in the list, even though they are now in the Windows / System 32 folder.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39599709
<It doesn't produce a PDF. >
Are you sure?
...in your code, check the destination.

Does the code even run?


And to be clear, ...Does my database, as posted, work?
Yes or No?

Does the Lebans sample from his site work?

Again, this all works fine for me in Access 2010


If this is just an issue with your database, then can you post a sample.

But if you don't have the time, ask MX to post his solution...

JeffCoachman
0
 

Author Comment

by:rick_danger
ID: 39599730
It's the very copy you sent me. The code runs, but doesn't produce output, and produces those lines of error as posted previously, all related to the DLLs, These are now in the Windows folder.
0
 

Author Comment

by:rick_danger
ID: 39599818
Jeff
OK - I now have it working, but only with the DLLs in the database folder.

Thanks for the solution.
0
 

Author Closing Comment

by:rick_danger
ID: 39599820
Good solution!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39599957
Glad I could help...
;-)
0
 
LVL 75
ID: 39600967
!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39602024
?
0
 

Author Comment

by:rick_danger
ID: 39602219
Don't know if those last 2 comments are meant to mean anything, but the code only works if the DLLs are in the database folder. This was the only reason it didn't originally work.
0
 
LVL 75
ID: 39602224
Actually, the !  was meant as follows:
You never responded to any of my posts :-(

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39604483
rick_danger,

MX has a point.

It is standard practice to at least reply t all experts.

Even if you don't agree with the methodology, at least you can say so.
It just a common courtesy.

I hate when it happens to me too...

So you can reopen the question if you like, then try MX's suggestions.
You may even find his solution to be more elegant than mine.

At the very least you will have another point of view.
Which is always a good thing.

;-)

JeffCoachman
0
 
LVL 75
ID: 39604755
no need to re open .... thx
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

706 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

20 Experts available now in Live!

Get 1:1 Help Now