Milestones Professional Automation: Microsoft Access VBA Examples

Warning: The information on these pages should only be used by software developers who are familiar with a Windows programming environment. KIDASA does not take responsibility for any damage caused to your information or computer due to programming errors.

Access VBA Example

Here’s an example of how you might use Milestones Professional’s OLE Automation to take information from an Access Table and generate a formatted project schedule. 
The “ScheduleData” Access table:
OutlineLevel Task Manager Funding2023 Funding2024 StartDate PercentComplete EndDate
1 Task 1 Jane $333.00 $123.00   0  
2 Task 1-1 John $234.00 $234.00 4/1/2023 44 6/30/2023
2 Task 1-2 Patrick $345.00 $345.00 5/1/2023 33 10/31/2023
2 Task 1-3 Glen $456.00 $456.00 7/15/2023 22 2/3/2024
1 Task 2 Mary $567.00 $567.00   0  
2 Task 2-1 Donald $999.00 $343.00 3/1/2023 99 6/30/2023
2 Task 2-2 Sue $342.00 $999.00 4/1/2023 44 6/6/2024
2 Task 2-3 Cariel $543.00 $434.00 5/15/2023 55 12/15/2024
2 Task 2-4 Jackie $3,000.00 $2,000.00 5/15/2023 33 12/1/2024
2 Task 2-5 Carl $342.00 $342.00 3/1/2023 22 4/1/2023
1 Task 3 Kirk $333.00 $333.00   0  
2 Task 3-1 Dave $444.00 $444.00 6/1/2023 15 6/30/2024
2 Task 3-2 Jane $555.00 $555.00 5/1/2023 23 5/1/2024
2 Task 3-3 Earl $332.00 $232.00 4/4/2023 33 6/19/2024
2 Task 3-4 Jane $332.00 $232.00 4/4/2023 44 4/20/2024
2 Task 3-5 Dave $332.00 $232.00 4/4/2023 55 6/6/2024
2 Task 3-6 Kirk $332.00 $232.00 4/4/2023 66 1/18/2024
2 Task 3-7 Carl $332.00 $232.00 4/4/2023 77 6/6/2024
2 Task 3-8 Jackie $332.00 $232.00 4/4/2023 88 5/10/2024
1 Task 4 Cariel $332.00 $232.00   0  
2 Task 4-1 Sue $332.00 $232.00 4/4/2023 22 7/29/2024
2 Task 4-2 Donald $332.00 $232.00 4/4/2023 33 11/26/2024
2 Task 4-3 Mary $332.00 $232.00 4/4/2023 44 12/29/2024
2 Task 4-4 Jim $332.00 $232.00 4/4/2023 55 8/4/2023
The Milestones Professional Schedule:
The Access Visual Basic Program:
Here’s a Visual Basic Program that generates an outlined Milestones Professional schedule using the data in the Access table.  The Milestones OLE calls are displayed in red.
Public Sub CreateSchedule()

‘ this function updates the schedule using data from a table

Dim dbsCurrent As Database
Dim rstTable1 As Recordset
Dim numberoftasklines As Integer
Dim numberofsymbols As Integer
Dim x As Integer
Dim x2 As Integer
Dim TaskNumber As Integer

‘Identify the table
Set dbsCurrent = CurrentDb()
Set rstTable1 = dbsCurrent.OpenRecordset(“scheduledata”, dbOpenTable)
Set objMilestones = CreateObject(“Milestones”)

With objMilestones
‘ Locate first record.
rstTable1.MoveFirst
‘ Activate Milestones Professional Schedule
.Activate
.Template “AccessTemplate.mtp”
.Refresh

TaskNumber = 0

‘Start of loop


Do Until rstTable1.EOF
TaskNumber = TaskNumber + 1

On Error GoTo SkipDate

‘Use Milestones Professional OLE Automation calls to add symbols to the schedule

.AddSymbol TaskNumber, Format(rstTable1!StartDate, “mm/dd/yy”), 1, 1, 2
.AddSymbol TaskNumber, Format(rstTable1!EndDate, “mm/dd/yy”), 2, 1, 2

.SetOutlineLevel TaskNumber, rstTable1!OutlineLevel

SkipDate:
‘Add information to the task columns
.PutCell TaskNumber, 1, rstTable1!Manager
.PutCell TaskNumber, 3, rstTable1!Task
.PutCell TaskNumber, 6, “$” + Str(rstTable1!Funding2023)
.PutCell TaskNumber, 7, “$” + Str(rstTable1!Funding2024)
.RefreshTask TaskNumber


‘Move to the next record
rstTable1.MoveNext
Loop

‘ End of loop.


.SetLinesPerPage TaskNumber
.SetTitle1 “ACCESS OLE AUTOMATION EXAMPLE”
.SetTitle2 “Milestones Professional”
.SetStartDate “1/1/2023”
.SetEndDate “12/31/2024”
.Refresh

‘Close Access Table
rstTable1.Close

‘Keep Milestones Professional schedule open
.KeepScheduleOpen
End With

Exit Sub

End Sub
To try this example:
Click here to download a self-extracting ZIP file containing:
 – The Access Database (AccessExample.mdb)
 – The Milestones Professional Template (AccessTemplate.mtp) 
(*NOTE: Place this .MTP template in your default Templates directory – Check Tools Tab > Folder button to see default Templates directory. Usually it is your Documents Folder)
 
(This download is recommended for experienced Access users with some programming knowledge.)

Click here to visit our main programmer’s page.