Access Automation Example #1

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	Funding1999	Funding2000	StartDate	EndDate
1	Task 1	Jane	$333.00	$123.00	2/10/99	9/14/00
2	Task 1-1	John	$234.00	$234.00	4/1/99	10/31/99
2	Task 1-2	Patrick	$345.00	$345.00	5/1/99	10/31/99
2	Task 1-3	Glen	$456.00	$456.00	7/15/99	2/3/00
1	Task 2	Mary	$567.00	$567.00	10/1/99	10/31/00
2	Task 2-1	Donald	$999.00	$343.00	3/1/99	6/30/99
2	Task 2-2	Sue	$342.00	$999.00	4/1/99	6/6/00
2	Task 2-3	Cari	$543.00	$434.00	5/15/99	12/15/00
2	Task 2-4	Jackie	$3,000.00	$2,000.00	5/15/99	12/1/00
2	Task 2-5	Carl	$342.00	$342.00	3/1/99	4/1/99
1	Task 3	Kirk	$333.00	$333.00	4/1/99	4/13/00
2	Task 3-1	Dave	$444.00	$444.00	6/1/00	6/30/00
2	Task 3-2	Jane	$555.00	$555.00	5/1/99	5/1/00
2	Task 3-3	Earl	$332.00	$232.00	4/4/99	6/19/00
2	Task 3-4	Jane	$332.00	$232.00	4/4/99	4/20/00
2	Task 3-5	Dave	$332.00	$232.00	4/4/99	6/6/00
2	Task 3-6	Kirk	$332.00	$232.00	4/4/99	1/18/00
2	Task 3-7	Carl	$332.00	$232.00	4/4/99	6/6/00
2	Task 3-8	Jackie	$332.00	$232.00	4/4/99	5/10/00
1	Task 4	Cari	$332.00	$232.00	4/4/99	6/12/00
2	Task 4-1	Sue	$332.00	$232.00	4/4/99	7/29/00
2	Task 4-2	Donald	$332.00	$232.00	4/4/99	11/26/00
2	Task 4-3	Mary	$332.00	$232.00	4/4/99	12/29/00
2	Task 4-4	Jim	$332.00	$232.00	4/4/99	8/4/99

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!Funding1999)
.PutCell TaskNumber, 7, "$" + Str(rstTable1!Funding2000)
.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/1999"
.SetEndDate "12/31/2000"
.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 EXE file containing:

- The Access Database (AccessExample.mdb)

- The Milestones Professional Template (AccessTemplate.mtp)

(This download is recommended for experienced Access users with some programming knowledge.)

For other examples, look at the Excel Example page. Anything that can be done in Excel can also be done in Access.

Click here to visit our main programmer's page.