?
Solved

Create PDF from Access Report in a Terminal Services Environment

Posted on 2004-03-27
8
Medium Priority
?
1,385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 65

Expert Comment

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

Author Comment

by:Data-Man
ID: 10696361
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 150 total points
ID: 10697366
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 65

Accepted Solution

by:
rockiroads earned 600 total points
ID: 10697831
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 10697881
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
ID: 10698484
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
ID: 10700075
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
ID: 10829893
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

752 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