Create a Milestones Schedule using Microsoft Access Data

Please Note: This document describes only one of the many possible ways schedules can be generated from Microsoft Office Access.  Please make note of the many ways this can be accomplished:
  • By copying the data from Microsoft Office Access and pasting it directly onto a schedule. (The easiest method)  Click here for details on this method
  • By using OLE Automation.  This method is great for programmers who want to integrate scheduling into their enterprise applications. Visit our Programmer's page to learn more.
 

Introduction

It’s easy to create Gantt charts from your Microsoft Office Access databases, or any program that supports .CSV (comma separated variable) files, using Milestones Professional. In many corporations, one department maintains an Microsoft Office Access database with dates and project resources while another department creates the schedules that are shown to clients and/or co-workers. Milestones makes it easy to create schedules using your Access data by the following steps:

 

Step-By-Step

This section describes how you can export the information from Microsoft Office Access using  Microsoft Office Access menus. The Full Automation section, found after the Step-by-Step section, describes how you can make the importing and exporting process automatic, so users of your application do not need to perform the detailed steps.

In Microsoft Office Access, your schedule information is stored in a Microsoft Office Access table. The following steps are involved:

Export the data to a comma delimited file

  1. Select the Export option on the File menu.
  2. For Data Destination choose "Text (Delimited)"
  3. Choose the table that has your schedule information. (Table only)
  4. Specify a file name with an extension (file type) of CSV.
  5. When you reach the Export Text Options dialog, click the OPTIONS>> button to reach additional options.
  6. For options, choose File Type: Windows (ANSI); Text Delimiter: None; Field Separator: "," (comma). Date Order and Date Delimiter should match those set via Windows Control Panel (usually Date Delimiter is / and Date Order is MDY).
 

Import the data into Milestones Professional

  1. Start Milestones Professional
  2. Open a template with enough columns to accommodate the information you plan to import, or add extra columns as needed to any empty schedule.
  3. Select File | Import Data | Custom Import.
  4. Locate and select the file you exported in step 1.
 

Mapping your Microsoft Office Access to data to a Milestones schedule

  1. Using Milestones Professional’s File | Import Data | Custom Import dialog, map your input file to the fields on your schedule.
  2. Remember, there are 10 columns on each side of your schedule. The column closest to the left side of your schedule is column 10.
  3. The first set of information from your data file is listed under the headings Fields from line 1 of input file (on the left), and the available Milestones Professional schedule fields are listed on the right.
  4. Map your task description to a column, such as column 10 (the column closest to your schedule). To map, click once on the information on the left, and then click once on Column_10 on the right. This will cause a link to appear. If you change your mind and want to remove a link, just double-click on mapped field.
  5. Map the Start Dates from your file to the Task1_Start_Date field.
  6. Map the End Dates from your file to the Task1_End_Date field.
  7. In the custom import, you can have up to 20 consecutive "tasks" on each project step. This allows you to show schedule information beyond simple start and end dates.
  • If you have information in your database for scheduled start/end and also information for actual start/end, you might map the scheduled start/end to the Task1_Start_Date and Task1_End_Date, and then map the actual start/end to the Task2_Start_Date and Task_2_End_Date.
  • Map any other sets of starts & stops that you want to appear (if any) on the same row using the additional start and end dates available. You can also map single dates to any of the Milestone Date fields available.
  1. If your data is not stored in date format, you can use the Task5 - Task8 fields to map the year, month, day, hour, and even minute.
  2. Choose Continue Import Using Current Filter. If asked if you want to save the filter, indicate YES.
    Your information should now be displayed on your schedule. If it did not appear, check the following:
    • If the project names did not appear in the column you thought it would, check to be sure you have it mapped to one of the columns that is currently visible on your schedule.
    • If the schedule information did not come in, check to be sure the date format matches the date format you are currently using in Milestones Professional (usually M/D/Y, for example: 10/22/97 for October 22, 1997).
 

Automating the Exporting/Importing Process

Microsoft Office Access developers can generate schedules automatically from Microsoft Office Access databases using two simple macro statements. Before implementing full automation, we recommend that you do the following:

  • Perform the steps outlined above.
  • Set up a Milestones Professional schedule and save it as a template (.MTP) file while in Milestones Professional.

In your Microsoft Office Access database, create a new macro with 2 statements:

1  The first statement should be TransferText with the following parameters:

Transfer Type: Export Delimited
Specification Name: (MS Access lets you save a specification name when you export files. If necessary, provide the name here.)
Table Name: The name of the table you are exporting. This is the table containing your schedule information.
File Name: Specify a file name, such as \ACCESS\schddata.csv
HasFieldNames: No

2 The second statement should be RunApp.

You might have many different templates set up for use by your Access application. For example, you might have one template that you will call up for presentation to someone who wants project detail, and one template for presentation to an executive who wants to view summary information.

If you have any questions or comments about the information in this document, please contact KIDASA’s product support at 512-328-0168, or support@kidasa.com.

 

OLE Automation

Using OLE Automation, you can obtain access to the individual objects within a Milestones Professional schedule, such as cells, symbols, titles, etc. This interface allows you to create and manipulate the elements of a schedule from other programs such as Visual Basic or VBA or any other OLE Automation Controller that conforms to the OLE 2.0 standards.

To learn more about OLE Automation, visit our Programmer's Page.