How do I setup a Dynamic Package Schedule for MS Access in MARS?

Dynamic Package Schedules allow you to create packages of at least two reports which share a common key parameter. This allows you to create a package of reports to be delivered to a unique destination.

Dynamic Package for MS Access

Dynamic Package Schedules allow you to create packages of at least two reports which share a common key parameter. This allows you to create a package of reports to be delivered to a unique destination.

Example: We will create a Dynamic Package to deliver the Marketing and Sales reports for clients and deliver this set to a unique destination.

How to Create a Dynamic Package Report for MS Access?

  • Go to Dynamic Package.
MS Access: MARS Home Menu

MS Access: General Wizard in Dynamic Package in MARS.

General Wizard

  • Package Name: The name of the package.
  • Create In: This section is for select the folder where you want to create the report in.
  • Description: Add a Description to the schedule to better identify it.
  • Keyword (optional): Enter some keywords which can be used later by Smart Folders to identify this schedule.

Click Next to go to the next wizard section.

Schedule Wizard

MS Access: Schedule Wizard in Dynamic Package in MARS.

In this section, you will decide when the report will execute. There are a variety of options:

  • Daily: Run a report every day or at a frequency of days.
    • Sub options: Repeat every X Days.
Example: Run the schedule every 3 days.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Weekly: Run a report on a weekly time frame.
    • Sub options: Repeat every X weeks.
Example: Run the schedule every 2 Weeks.
  • On: Select the specific days of the week the schedule will run. If only once a week,select only the day of the week it will run.
Example: Run every Monday, Wednesday, and Friday.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Week Days: Run the schedule Monday through Friday.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Working Day: Run the schedule starting on a specific day of the month. Indicate which day of the month the schedule will run. E.G. run the schedule on the 4th working day of the month.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Monthly: Run the schedule on a monthly time frame.
    • Use the following options: Checking this box will enable you to select frequency options such as the “last Thursday of the month.” Also you can include or exclude specific months from the schedule.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Annual: Run the schedule every year at a specified time.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Custom Calendar: Select the custom Calendar you wish to use. You can create a new custom calendar from the menu as well. Please see Custom Calendars for more information.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Other: Other scheduling options.
    • Run Schedule every X Minutes, hours, days, weeks, months, years.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • None: No scheduling is required for this item.
MS Access: Schedule Wizard in Dynamic Package in MARS.
  • Destination: Select how you would like your report to be delivered. This will also determine some of the options that will show up later in the process. For example, if you select email, you will be asked for email address later.
  • Start Date: Enter the desired starting date for the schedule. This section can be the current date (providing schedule time has not already passed) or a date in the future.
  • End Date: If the schedule is due to end after a certain date enter that here. If the schedule is to run indefinitely, then leave it blank.
  • Schedule time: On the Next Run date, the package will run at this time.
  • Exception Calendar: Choose a calendar that will instruct the schedule to NOT run on those specified days. Please see Custom Calendars for more information.
  • Next to run on: The package will next run on this date.
  • Repeat Every: Rerun the package every x minutes from the scheduled time until your specified time.
For example, you can set up a daily package to run every day at 8 am, and to run every hour until 5 pm.
  • Until: After this date, there will be no automated scheduling of this package.
  • Enable this Schedule: Uncheck this option to Disable the package. Disabled packages are not deleted, but they do not execute automatically. You can re-start the automatic scheduling at a later date by checking this option again. Or right-clicking this schedule from the main screen and selecting Enable.

Click Next to continue to the next wizard section.

Reports

MS Access: Reports Wizard in Dynamic Package in MARS.

A package may be created with no constituent reports and then the reports can be added later. However, you may also add reports at this stage in the wizard - and amend, delete or add some more later.

  • Merge all PDF Files: Merge PDF outputs into a single PDF file. The reports will appear in the merged PDF in the order they are displayed in the package schedule. Checking this box will bring up additional PDF Options.
MS Access: Report Wizard in Dynamic Package Schedule in MARS.
  • Merge all Excel Files: MARS will then merge all excel outputs in the package into a single excel file.
MS Access: Report Wizard in Dynamic Package Schedule in MARS.
HINT: Add Password Protection to this section in this option window rather than the individual report in the package. This will password protect the entire merged workbook.
  • Click Add
  • Package Report Properties will appear.

Package Report Properties

Report Wizard

MS Access: Package Report Properties in MARS.
  • Report Name: Name the schedule.
  • DB Location: Browse and select the database where the report is located.
  • DB Password: Enter the password to the selected database if necessary.
  • WorkGroup Security: If your Access database uses Workgroup Security, then check this option and enter the required credentials.
  • Security File Path: Browse and select the security file (.mdw) which is used by Access to control security for this database. If you don't know what this is, or where it is stored, your System or Database administrator will be able to point you to the right file.
  • Important If you are using Workgroup Security:
    • You must join the workgroup that defines the user accounts used to access the database.
    • Your user account must have Open/Run and Open Exclusive permissions for the database object.
    • Your user account must have Administrator permissions for all the tables, queries, macros, tables and any other objects in the database, or it must be the owner of all tables in the database.
    • Your user account must have Administrator permissions for all objects in the database.
  • Report or Query: Select Report or Query to tell MARS which you wish to schedule.
  • Connect: Click to connect to the database.
  • Report Name: Select the report or query you wish to schedule from the dropdown list (only available after successful connection to the database)
  • Open Arguments: If there are some special arguments you would like MARS to run when it opens the MS Access database you should enter them here.

Output Format

MS Access: Package Report Properties in MARS.

Parameters

MS Access: Package Report Properties in MARS.
  • In this section, you will determine the parameters for your report (if any). If your report has no parameters, you may skip this section by clicking next.
  • There are 3 requirements in order for MARS to detect your MS Access Report parameters and their parameter values:
    • The parameter must be in use in the report.
    • The parameter must be visible.
    • The parameter must be set to be prompted at run time.
  • If these three requirements are achieved, you should see the parameters in your report listed in this screen.
  • For each parameter, select from the drop down list the value the report must use. In a single schedule, only one parameter value can be run at a time per parameter. You can type a value into the field, or drag and drop an "insert".
Tip: to enable a report parameter to run for a date constant (Current Date, Yesterday etc.), select MARS Constants from the inserts window, then drag and drop your desired constant to the parameter field.
  • Preview: View the report output given the selected parameters.
  • Advanced: Shows you the report's record selection formula and allows you to edit it:
MS Access: Package Report Properties in MARS.
  • To modify the record selection formula, simply type your modification into the formula space. Once this formula is changed, MARS will always use this formula for this schedule. It will ignore the original report's formula.
  • The changes are stored in MARS. The report in your database is not changed, so it can be reused for other schedules or for its previous general use.

Naming

MS Access: Package Report Properties in MARS.
  • This is the option where you named the output file.
  • Default Naming Convention: MARS will name the output file in the following format: reportname.format extension, e.g. Catalog Report.pdf.
  • Customize the output file name: Choose your own filename or right-click and use the Insert Function to insert a value.
  • Customize output extension: Choose your own extension. This is useful for system integration. For example, the default extension for a character separated file is "CSV," but you can give your export an extension of "txt" so that the file can be read by another already existing system you may have. You may also right-click and use the Insert Function to insert a value.
  • Append date/time: This is useful for the following reasons:
    • If the filename is the same each time, and it is being exported to the same folder each time, then it will be overwritten by the latest one each time. By appending date and time to the filename, each file remains unique, and no files are overwritten.
    • You can track which reports ran and when they ran by looking at what the report is named.

Report Options

MS Access: Package Report Properties in MARS.
  • In this section you will authenticate to the database the report runs against, set snapshots, and other options.
  • Login required for linked tables (optional): If the database requires logon credentials, check this option and enter the required username and password. If credentials are not required, uncheck the option and continue as normal (default). All values are optional. You only need to enter a value if your database, security, networking or infrastructure require it. Logins for individual tables can be set as well.

Lock Prevention

MS Access: Package Report Properties in MARS.
  • Use a Copy: An Access databases will become unstable if multiple users are connected at the same time as an automation tool is interacting with it. To prevent this we recommend that MARS works exclusively on a copy of the database when it needs to run a report or query. Check this option to enable MARS to use a copy of the source database.
  • When the option is selected a copy of the database will be taken and stored in the same folder as the source database. You can change the location of the copy to a location with more disk space. If your database is stored on a network, you will benefit from better performance if the copy is stored locally on the MARS PC.
  • When MARS has finished running your report it will delete the database copy. It will be able to do this once MS Access has released the file (the ldb disappears). This can take a while, sometimes up to a few hours, so be patient.
  • If you prefer not to use a copy of your database (e.g. because it is too big) then youshould not check this option. However you must ensure that no other users or processes are logged into the database at the schedule time. In addition, we recommend that you backup up your database before the schedule time so that you can revert to your original in case of corruption.
  • Front-End/Back-End: This is a special type of database which has been split using the facility in MS Access and is Identified by MS Access as FE/BE. For more information about splitting your database please see your MS Access user manual. Select this option if you are using a split database.
When splitting your database MS Access stores the path to the BE in the FE. During the slitting process ensure you select a UNC path to the BE otherwise it will only be visible to MARS if MARS is installed on the PC where the splitting was done.
  • If your backend database is an ODBC database, select this option, the Database DSN, and your user credentials.
  • If your backend database is an Access file, select this option, then browse to the desired backend database file.

Exception Handling

MS Access: Package Report Properties in MARS.
  • Treat as “error” if not completed in X minutes: If a report takes longer than the specified amount of time to run, this option will treat the schedule as an error and follow the appropriate action. The “Auto-calculate” option instructs MARS automatically determine how long a schedule should take to run the report. If it takes longer than the calculated amount of time, then it is an error.
If manually determining the error timing, please double check the run time of the report in order to get the correct time estimate.
  • On error, retry executing schedule every: If set to 0, MARS will deem the schedule as "Failed" the first time it encounters an error. The schedule will not run again until its next scheduled time. Change the value to tell MARS how many times you want it to retry running the report before declaring it as "Failed."
  • Check if the Report is Blank: If a report is blank because it genuinely returned no data, recipients can misconstrue this as an error with the scheduler. This option allows you to identify genuine empty reports and instruct MARS on what to do with them.
  • Ignore the report and subsequent tasks: if the report is blank, do not send the report. The report will not be delivered to the destination. No custom tasks will be run.

Method

MS Access: Package Report Properties in MARS.
  • Select the Method that will determine whether a report is blank.
  • Native: MARS will check to see if the report returns any data. If not, the report is considered blank.
MS Access: Package Report Properties in MARS.
  • SQL Query: Select this option to use a user made query that will determine if the report is blank. If the query returns no results, the report is blank.
  • Click Build.
  • Get values from a database window will appear. For more information about Get values from database, click here.
MS Access: Get values from database interface in MARS.

Actions

MS Access: Package Report Properties in MARS.
  • Select an action from the task list. This task will be executed in the event that a schedule is blank.
  • For more information about tasks, click here.
Tip: You can send a notification if a report is considered blank instead of sending the report. Simply select “check if a report is blank” then select “Ignore the report.” In the actions tab, select “Send Email” from the list. Compose your email and save.

Click OK.

Click Next to continue to the next wizard section.

Key Parameter Wizard

MS Access: Key Parameter Wizard in Dynamic Package in MARS.

On the Key Parameter tab, you will begin by selecting the report parameter that will hold the unique value being “looked up” by your database.

  • Populate key parameter with static data: This option would be chosen if you DO NOT want MARS to feed multiple parameters in to the report via database. Rather, one parameter value will be chosen, and it will run for a changing list of recipients.
  • Populate key parameter with data from a database: This is the most common option for users creating dynamic schedules. MARS will use a query to automatically feed key parameter values into your report based on a table.
MS Access: Get values from database interface in MARS.
  • You should now notice your report parameter being populated with an insert. This means MARS will reel through your table, pull the Key parameter values and feed them into your report. A unique file will be generated for each value.

Linking Wizard

MS Access: Linking Wizard in Dynamic Package in MARS.

In this Section we will establish a link between the key parameter values we have selected earlier, and the location of the corresponding destination information.

  • Use static destination: This will export all your report to a single destination e.g. a folder.
  • DSN Name: Drop down and select a DSN.
We strongly recommend System DSNs so that the DSN is visible to MARS even when the user associated with the DSN is logged off.
All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.
When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection). This ensures that your username and password are not required to be stored in MARS (more secure) and overcomes a large number of security restrictions which Windows places on DSNs. When using Windows Authentication, make sure that the MARS NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.
When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").
  • UserID: Enter the User ID MARS should use to log on to the database
  • Password: Enter the password associated with the above user.
  • Connect: Connect to the Database
  • Select the table name and the column in that table which matches the parameter value which you determined in the previous step.
  • Select the column in the table that holds the email address (or folder path or printer name) that corresponds to the above parameter value.

Click Next to continue to the next wizard section.

Destination Wizard

Under the Destinations tab, the destination that you chose previously under the Schedule section will now automatically pop-up. Secondly there is no ‘To’ address because this information is being retrieved from a database. The reason for this is that since the destination type was defined in the previous step.

MS Access: Destination Wizard in Dynamic Package in MARS.
  • Use the Inserts Function to customize the email subject and body as desired. Simply drag and drop Dynamic Table Fields from your Inserts list.
  • Select the format for your report.
MS Access: Destination Wizard in Dynamic Package in MARS.

Click Next to continue to the next wizard section.

Exception Handling

MS Access: Exception Handling Wizard in Dynamic Package in MARS.
  • Treat as “error” if not completed in X minutes: If a report takes longer than the specified amount of time to run, this option will treat the schedule as an error and follow the appropriate action. The “Auto-calculate” option instructs MARS automatically determine how long a schedule should take to run the report. If it takes longer than the calculated amount of time, then it is an error.
If manually determining the error timing, please double check the run time of the report in order to get the correct time estimate.
  • On error, retry executing schedule every: If set to 0, MARS will deem the schedule as "Failed" the first time it encounters an error. The schedule will not run again until its next scheduled time. Change the value to tell MARS how many times you want it to retry running the report before declaring it as "Failed."
  • Check if the Report is Blank: If a report is blank because it genuinely returned no data, recipients can misconstrue this as an error with the scheduler. This option allows you to identify genuine empty reports and instruct MARS on what to do with them.
  • Ignore the report and subsequent tasks: if the report is blank, do not send the report. The report will not be delivered to the destination. No custom tasks will be run.

Method

MS Access: Exception Handling Wizard in Dynamic Package Schedule in MARS.
  • Select the Method that will determine whether a report is blank.
  • Native: MARS will check to see if the report returns any data. If not, the report is considered blank.
MS Access: Exception Handling Wizard in Dynamic Package Schedule in MARS.
  • SQL Query: Select this option to use a user made query that will determine if the report is blank. If the query returns no results, the report is blank.
  • Click Build.
  • Get values from a database window will appear. For more information about Get values from database, click here.
MS Access: Get values from database interface in MARS.

Actions

MS Access: Exception Handling Wizard in Dynamic Package Schedule in MARS.
  • Select an action from the task list. This task will be executed in the event that a schedule is blank.
  • For more information about tasks, click here.
Tip: You can send a notification if a report is considered blank instead of sending the report. Simply select “check if a report is blank” then select “Ignore the report.” In the actions tab, select “Send Email” from the list. Compose your email and save.

Click Next to continue to the next wizard section.

Custom Tasks

MS Access: Custom Tasks Wizard in Dynamic Package in MARS.
  • In the section you have the option of setting up custom tasks. Custom tasks are business process automation tools that can be auto triggered before or after a report runs.
  • For more information on Custom Tasks, click here.
  • If you have no desire to add a Custom Task, you can click finish to complete the schedule.

Dynamic Package Context Menu

MS Access: Dynamic Package Context Menu in MARS.

Right-Click on a schedule to see the following actions:

  • Properties: Edit the schedule from here. Or you can just double click on the schedule.
  • Open: This will open the package and show its constituent reports.
MS Access: Dynamic Package Context Menu in MARS.
  • You can right-click on each of the constituent reports to see the context-sensitive menu shown in the picture above.
    • Enabled: Use this to enable or disable the constituent report.
    • Refresh: Pulls through changes to just that report which were made outside MARS.
    • Rename: Renames the selected report.
    • Preview: Generates a preview of the selected report.
    • Delete: Deletes the selected report from package.
    • Properties: Displays the configuration properties of just that report.
  • Add Report: Use this to add one or more reports to an existing package.
  • Copy: Use this to copy the schedule. Right-click in the "white space" of the folder you wish to copy it to and select Paste button.
  • Rename: Rename the package.
  • Enabled: Schedules are enabled when there is a check icon beside this option. To stop a schedule from running, or to "pause" it for a while, select this option to remove the check icon. Disabled schedules will not run until they are enabled again.
  • Move: Use this option to move the report into an existing package.
  • Execute: This button will execute the schedule immediately. Note that the next run date and time is not moved on as a result of a manual execution. They only move on if the schedule is run automatically by one of the schedulers .
  • Delete: Selecting this option will delete the schedule.
  • Test Package: Use this option to test the schedule and export it to selected "test" destinations.
  • Split into Single Schedules: This will split all the constituent reports in the package into Single Report Schedules.
This process will automatically delete the package once the splitting process is completed.
  • Ad-Hoc Email to Recipients: Select this option to send an ad-hoc email to all recipients of this package. You can use this to alert recipients to a planned system outage, or any other useful information.
  • Create Shortcut: Use this option to create a shortcut you can save in any location on your PC. Execute the shortcut to execute the schedule in MARS.

Dynamic Package Properties

MS Access: Dynamic Package Context Menu in MARS.
  • To access your schedule properties, right click on a schedule and select properties.
  • Similar to the schedule wizard, you adjust settings to your schedule such as timing, error handling, or custom tasks.
  • General
MS Access: Dynamic Package Properties in MARS.
  • Schedule
MS Access: Dynamic Package Properties in MARS.
  • Reports
MS Access: Dynamic Package Properties in MARS.
  • Output
MS Access: Dynamic Package Properties in MARS.
  • Exception Handling
MS Access: Dynamic Package Properties in MARS.
  • History
MS Access: Dynamic Package Properties in MARS.
  • Tasks
MS Access: Dynamic Package Properties in MARS.
  • Linking
MS Access: Dynamic Package Properties in MARS.