ACCESS report/.pdf file: Microsoft, ACCESS, 2003, Reports from ACCESS, VBA, windows

by: LSMConsultingPosted on 2008-08-06 at 17:59:58ID: 22176650

This is impossible to answer without seeing the relevant code. If you can post the code where the PDF is created, we might be able to instruct you on how to close that PDF ...

by: z018196Posted on 2008-08-07 at 07:06:44ID: 22181052

Ok, thanks, here is the .pdf generation function

Public Function RunReportAsPDF(LocType As String) As Boolean
' Returns TRUE if a PDF file has been created
 
    Dim AdobeDevice As String
    Dim strDefaultPrinter As String
 
    'Find the Acrobat PDF device
    AdobeDevice = GetRegistryValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Devices", "Adobe PDF")
 
    If AdobeDevice = "" Then    ' The device was not found
        MsgBox "You must install Acrobat Writer before using this feature"
        RunReportAsPDF = False
        Exit Function
    End If
 
    ' get current default printer.
    strDefaultPrinter = Application.Printer.DeviceName
 
    Set Application.Printer = Application.Printers("Adobe PDF")
 
    'Create the Registry Key where Acrobat looks for a file name
    CreateNewRegistryKey HKEY_CURRENT_USER, "Software\Adobe\Acrobat Distiller\PrinterJobControl"
 
    'Put the output filename where Acrobat could find it
    SetRegistryValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat Distiller\PrinterJobControl", _
                     Find_Exe_Name(CurrentDb.Name, CurrentDb.Name), "C:\pdf_docs\" & LocType & ".pdf"
 
    On Error GoTo Err_handler
    
    'produce report
    DoCmd.OpenReport "Output", acNormal
    
    CopyFile "C:\pdf_docs\" & LocType & ".pdf", "C:\pdf_docs\docs\" & LocType & ".pdf", False
 
    Do Until VerifyExistence("C:\pdf_docs\docs\" & LocType & ".pdf") = True
        Call CopyFile("C:\pdf_docs\" & LocType & ".pdf", "C:\pdf_docs\docs\" & LocType & ".pdf", False)
        DoEvents 'uncommented
    Loop
    
    If Len(Dir("C:\pdf_docs\" & LocType & ".pdf")) > 0 Then Kill "C:\pdf_docs\" & LocType & ".pdf"
 
    RunReportAsPDF = True   ' Mission accomplished!

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:

Select all Open in new window

by: boag2000Posted on 2008-08-26 at 15:50:47ID: 22320516

z018196,

I see the command to Open the report:
Line 31:
DoCmd.OpenReport "Output", acNormal
...but I see no corresponding code to close it?

Also you are referencing a Function called "CopyFile", can you post that code as well?

JeffCoachman

by: z018196Posted on 2008-09-04 at 13:44:23ID: 22392511

Thanks for the response Jeff,
I didn't know there was code that closed an access report. Could you post that? The CopyFile function just copies the pdf from where the pdf gets stored to a new location. I did this so that I can then delete the pdf that was in the original location. This helped with the .pdf's not opening up. Maybe the close code will solve that.

by: boag2000Posted on 2008-09-04 at 14:08:45ID: 22392728

z018196,

Here is the generic code to close your Report, where you should put this in your code, you will have to figure out.

Still, I cannot be sure it will solve the issue.

DoCmd.Close acReport, "Output"

JeffCoachman

by: z018196Posted on 2008-09-05 at 08:22:35ID: 22399935

Thanks Jeff,
I added that to my code, but it doesn't seem to be working correctly. All I'm trying to do is create a couple thousand reports(same template) and save each one in a .pdf format. Each report, when opened, has an object that houses all of it's data. The data is placed in the report and then, with my application, gets saved to the local drive, then gets copied to the network and then the one on the local drive gets deleted. I can't seem to figure out why 1 or 2 out of every 500 or so reports, opens up in the window and causes the program to break because it is unable to delete a file that is open. Maybe instead of copying and deleting, I could just save the pdf's to the network. Have you ever heard of this happening when trying to convert ACCESS reports to pdf's?

by: boag2000Posted on 2008-09-05 at 10:41:09ID: 22401606

z018196,

A few things to check.

Although all PDF's have a .pdf extension, new features are introduced with each version of Adobe.
If the files that fail were all created with a different version, that might be the reason.

Do the files in question have anything in common?
(Size, date, Location) that could have something to do with this.

Sometimes things like this are "timing related".
You may have to strategicly place one or more "DoEvents" in your code.
Where exactly, I cannot be sure.
In any event, if you understand what the code is doing, you can research Doevnts on the Web.

" Have you ever heard of this happening when trying to convert ACCESS reports to pdf's?"
Not really, because Access Report are "Dynamic", so for the most part, PDF's made from Access Reports are only good as snapshots anyway.

JeffCoachman

by: z018196Posted on 2008-09-12 at 07:15:24ID: 31483951

Thanks for your feedback Jeff. I believe it was a timing issue and with the copying and deleting tha was originally in the code, processess weren't being finished correctly. I took out all that code and just opened the report, saved it to a folder and then closed the report. By doing that, the process time nearly got cut in half and I have yet to recieve any errors.

by: boag2000Posted on 2008-09-12 at 08:15:17ID: 22460637

Great!

Congratulations for getting it worked out on your own.
;-)

BTW, in the future you can create a post outlining the solution and actually "Accept" your own post.
;-)

JeffCoachman

Question

ACCESS report/.pdf file

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Microsoft

ACCESS

2003

Reports from ACCESS

VBA

windows

Access Reports

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press