how can I dynamically specify the output location of an MS ACCESS report

Posted on 2013-05-21
Medium Priority
Last Modified: 2013-06-07
I have a system of about 30 programs - ACCDBs.
They all output reports as PDFs to a fixed location:

How can relocate the myapp folder and have the reports output to the new location such as

Question by:philkryder
  • 2
  • 2
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39186520
The output location would have been saved when you created the reports. If the output is generated in VBA, you need to edit it there in each case, if in a macro (possibly in the form you create the report from, edit it there.

LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 39186935
Along the lines of what Kelvin has posted, our databases have a table called "tblPaths" to store paths like this that are needed in various places in the code.  The fieldnames show the different "types" of paths, and there is a single record containing the actual paths... so the table might look like this:


FieldName                    Data
PDFReportPath             h:\myhomedirectory\myapp\output
webPath                        www.mysite.com
WorkOrderInputFiles   h:\myhomedirectory\myapp\workorders
ImagesPath                  h:\myhomedirectory\myapp\Images

When needed, these paths can be looked up in the code... for example:

Dim strOutputPath as String
Dim strFile as string
strOutputPath = DLookup("PDFReportPath", "tblPaths")
strFile = strOutputPath & "\" & Format(Date,"yyyy_mm_dd_") & "myReport.pdf" 
' Output a report...
DoCmd.OutputTo acOutputReport, "rptMyReportName", acFormatPDF, strFile, True

Open in new window

This allows you to easily change the path for the reports -- just by editing the path as stored in the table.
LVL 50

Expert Comment

by:Dale Fye
ID: 39187152
no points please.  Minor correction to mbizup's code:

strOutputPath = DLookup("Data", "tblPaths", "[FieldName] = 'PDFReportPath'")
LVL 61

Expert Comment

ID: 39187181

My code was correct... but the explanation may not have been clear.  I used that format because of a lack of horizontal space.  It might be clearer in a code snippet.

tblPaths contains a single record.  The FieldNames in my previous post are the actual columns in that table.  So pictured another way, the DATA in tblPaths is:

PDFReportPath                             webPath                       WorkOrderInputFiles           ImagesPath  

h:\myhomedirectory\myapp\output         www.mysite.com           h:\myhomedirectory\myapp\workorders        h:\myhomedirectory\myapp\Images       

Open in new window

LVL 50

Expert Comment

by:Dale Fye
ID: 39187193

I get it, your Paths table contains a single record and multiple fields, very "non-normal" of you.  I should have figured that out when I saw the "FieldName" header on your list.


Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

624 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