How do I add new MS Access reports to Event Based Schedules in MARS?

Using an Event Based schedule, you can trigger an existing schedule, or a new report, based on events. New reports allow you to use "insert" data from the event that triggered the schedule.

New Reports

New MS Access Report in Event Based Schedule using MARS
  • Select New Reports and click Next.
New MS Access Report in Event Based Schedule using MARS
  • Click Add.
  • Report Properties will appear.

Report Properties

Report Wizard

MS Access: Event Based 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.

Parameters

MS Access: Event Based 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: Event Based 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.
Tip: If you are looking to Dynamically populate parameter values, the better choice is the Dynamic or Data-Driven Schedule.

Report Options

MS Access: Event Based 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: Event Based 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: Event Based 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: Event Based 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: Event Based 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: Event Based 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.

Custom Tasks

MS Access: Event Based Report Properties 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.

Destinations

MS Access: Event Based Report Properties in MARS.
  • Add: Click here to add a destination. You have several options which are: Email, Disk, Fax, FTP, ODBC, Printer, Sharepoint, and SMS.
MS Access: Event Based Report Properties in MARS.
  • Edit: Select a destination and click to edit it's properties. Or simply double-click on the destination.
  • Delete: Select a destination and click this button to delete it.
  • Import: click here to import from the list of default destinations.
  • For more information about Type of Destinations, click here.
  • For more information about Output Formats, click here.

Click OK.

Customize Recipient Messaging

  • You can also customize the messaging the recipient receives. Using Event Based Data Inserts, MARS will automatically pull data from the record that triggered the schedule and use it to customize the subject, body or report format.
Using Event Based Data Inserts in Email Destination in MARS
  • Simply drag and drop fields from your table to the desired position.
For example, to customize an email greeting, type the greeting, and add the recipients name as shown above.