3

I am using MS Access 2003 and have seven different reports I have to distribute each morning, and I currently do that manually by printing each to an XPS file. The default printer for each report has been set to XPS file, and I tried this code:

DoCmd.OpenReport ("rptDaily_1of7")
DoCmd.PrintOut acPrintAll,1,,acHigh,1

When I use that, it does open a Save As box, but .tif is the only option in the "Save As type" field. Even if it wasn't though, ideally I'd like to send the file path and name as a parameter and have it handle the entire process (so I can use a loop to generate all seven files with the push of a button). I'm hoping there's another command I can use to do this, or possibly another method. Note that it's not my machine, and I do not have admin rights to install anything else (like a pdf generator, etc.).

rryanp
  • 1,027
  • 8
  • 26
  • 45

1 Answers1

2

You can create your reports as PDF files using ReportToPDF by Stephen Lebans.

This can be easily automated (there is a MDB with example code in the download) and you don't have to install anything.
It comes with two DLLs which you need to put into the same folder as your MDB/MDE, and that's it. No installation / DLL registering, just distribute the two DLLs with your MDB/MDE.

We are using this at work to print thousands of reports per day from Access 2003.

Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • This looks great, Christian--thanks! Unfortunately I'm working on a customer machine that I cannot get files onto, so I haven't been able to test it yet. I'm working on getting approval to move the dlls onto that machine, so I'll let you know how it goes. I appreciate it. – rryanp Jul 13 '12 at 19:26
  • How do you distribute new versions of your app to the machine? Can't you just distribute the dlls **with** your app? That's the way I do it. Remember, no registering or stuff like that. Copying the files to the same folder like your app is enough – Christian Specht Jul 14 '12 at 09:14
  • I am building the DB on a laptop that connects to the network, but I can't access the internet or get email attachments to the laptop. I was able to get the DLLs approved though, and I have them in the same folder as the MDB file, but I get a "Sub or Function not defined" error on "ConvertReportToPDF". I haven't started troubleshooting it, but let me know if you have any suggestions. Thanks! – rryanp Jul 16 '12 at 14:44
  • I learned that in addition to moving the DLL files, you also have to copy the module "modReportToPDF" from the sample database to your database (it actually contains the "ConvertReportToPDF" function). Once that was done, it worked like a charm. Thanks again. – rryanp Jul 16 '12 at 15:26