Solved

Create PDF from Access Report in a Terminal Services Environment

Posted on 2004-03-27
8
1,354 Views
Last Modified: 2008-02-26
I have run into a wall with trying to programatically create PDF's from Access Reports.

We are running Adobe Acrobat version 6 (try before you buy version)
Windows 2003
Access 2002/XP
Windows Terminal Server

I do development on the database within my TS session.  

I've modifed all the registry settings to set Adobe PDF Writer as the default printer.  I've also set the PDFFileName value.  As I step through the code, I have verified that the resgistry settings are being set.

My problem is that the Access report Defaults to My Documents\*.pdf, it does not use the file namein PDFFileName and according to the documentation I have found, that reg setting is reset after each write.

Amy I missing Something....Here is what I currently have.

    ......
    'Save current default printer
    strMyDefPrinter = RegRead("Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")
    'Set default printer to PDF Writer
    RegWrite "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device", "Adobe PDF,winspool,Ne02:"

    SetKeyValueRegWord "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers\Adobe PDF\PrinterDriverData", "PromptForFileName", 0, REG_DWORD
    SetKeyValueRegWord "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers\Adobe PDF\PrinterDriverData", "ViewPrintOutput", 0, REG_DWORD
    SetKeyValueRegWord "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers\Adobe PDF\PrinterDriverData", "AskToReplacePDF", 0, REG_DWORD
    'Setting value for PDFFileName in the registry stops file dialog box from appearing
    RegWrite "Software\Adobe\Acrobat PDFWriter", "PDFFileName", "\\MVIConnect\sellsmart\ServiceTrack\" & Me.Project & "\" & strName

    'Run the report
    DoCmd.OpenReport "rptServiceTrackWorkOrderPDF", acViewNormal
   .....

   I've even tried making a copy of the report and setting it's printer specifically to Adobe PDF, same problem.

   I'm sure it has something to do with a registry setting.  But not being a WTS expert, I'm missing something.  I've searched the web for 2 days to solve this problem to no avail.
   I found tons of comments on this website, but none of the 98 solutions were using WTS.  I found a similiar thread, but it doesn't provide me with a working solution.  By the way, sendkeys or something hokey like that is not an option.

  Any insight into this problem would be greatly appreciated.  
 
Thanks,
Mike (aka Data-Man)
0
Comment
Question by:Data-Man
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
would something like DoCmd.OutputTo do instead of OpenReport?
you can specify location with OutputTo,
0
 
LVL 18

Author Comment

by:Data-Man
Comment Utility
unfortunately, the pdf is not available as an option in the OutputTo method.

I did find a work around...not very happy with the solution and would like it to work as the documentation explains.

I ended up using the Name function to rename the file to the correct directory and name....introduced its own little problem.  After the report was generated, the Name function was executed.  But, the file wasn't there yet...took a second or two for it to show up in the My Documents directory before I could copy it over.  Had to put a delay in of 2 seconds before I could run the Name function.

Also had a a problem in that we are using Terminal Services, no roaming profiles.  Every 'My Documents' directory is on the server so I had to get the NT login name and then build the string for the Name function.

It seems to be working for now, but I'm not happy with the code...very sloppy.

Now I need an API call to get the My Documents directory so the poeple outside of Terminal Services won't get an error.

Thanks,
Mike
0
 
LVL 11

Assisted Solution

by:Stephen_Perrett
Stephen_Perrett earned 50 total points
Comment Utility
Hi

You can retrieve the path of the My documents folder of the user using when you first start your Access Database with

CurDir$

CurDir$ returns my personal My Documents folder for me on Windows XP and I would expect that it would also work through terminal services.

 I hope this can help.

I am currently struggling with saving Access reports to PDF using Adobe PDF myself but not through Terminal Services. I have found ways that give me results also and I'm sure there must be a better way than the one I'm using. The method your using looks more sophisticated than mine and I think it will assist me in my efforts. So thanks.

Steve

0
 
LVL 65

Accepted Solution

by:
rockiroads earned 200 total points
Comment Utility
The registry entry that holds folders for user preferences is held in

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

under there is a key called Personal (amongst several others)

you could try mod that then mod it back again

Steve's suggestion gave me an idea but didnt seem to work - I tried changing directories within code (ChDir) then run print, no luck
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
there is some code here which sets a printer, this might be helpful for you


http://www.experts-exchange.com/Databases/MS_Access/Q_10118914.html?

0
 
LVL 18

Author Comment

by:Data-Man
Comment Utility
I'm back at the grind stone again this morning.  Thans to Stephen for the CurDir$.  It does give me exactly what I wanted.

I ran into another problem.  I logged out last night and when I logged in this morning, when I print to the PDF Writer, I get a print error.  From the Main box, it works, but from the session in WTS, it doesn't work.

I uninstalled 6.0 and reinstalled it this morning.  That seems to have fixed the problem.  I'm going to rethink the logic today and when I get a working solution, I'll post it here.

Thanks to all for your help.
Mike
0
 
LVL 18

Author Comment

by:Data-Man
Comment Utility
I'm at a loss to figure out a registry setting solution.  It would seem that the registry settings for Adobe 6.0 aren't the same as earlier versions and changing them via code doesn't seem to have an effect when in a terminal services enviornment.

Here is the solution I came up with...although not completely happy...too much room for error.

I created a duplicate Access report that is set to a specific printer (Adobe PDF Writer).  
I then changed the settings of Adobe PDF Writer by unchecking the following.

View Adobe PDF results
Prompt for Adobe PDF filename
Add document information
Ask to replace existing PDF file.

Then just use DoCmd.OpenReport "ReportName, acVeiwNormal

the file is placed in the My Documents directory.  I then used an API call get the "My Documents" directory and then I copy the file (it has the same name as the caption of the report) to the correct location and filename using the 'Name' function.

Until I can figure out how to correctly modify the registry to make this work, I will have to live with this solution.

Thanks for all your help.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
Check this web site out.  e-Print 4 solved my access to pdf problems.  It installs and works like a printer driver.

                                 http://www.downlinx.com/proghtml/293/29359.htm
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 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

11 Experts available now in Live!

Get 1:1 Help Now