you can use
VBA to automate ms project from ms access. so in other words if you
had several tables of events with dates and other fields in access you could
automate the creation of tasks based on these records in ms project. following
is some code I did a while ago to achieve this.
- Matt
Function ExpToProj()
Dim Test As String
Dim iCount As Integer
Dim Total As Integer
Dim Proj As Object
Dim Proj1 As Object
Dim Proj2 As Object
Dim DB As Database
Dim RecSet As Recordset
Dim Tasky As Object
Set DB = DBEngine.Workspaces(0).Databases(0)
Set RecSet = DB.OpenRecordset("TableExample", DB_OPEN_TABLE)
Set Proj = CreateObject("MSProject.Application")
'Dim iCount As Variant
Proj.Application.Visible = True
Proj.FileOpen "H:\WORK\file.MPT"
RecSet.MoveLast
Total = RecSet.RecordCount
RecSet.MoveFirst
iCount = 1
Do Until RecSet.EOF
If IsNull(RecSet.Fields("ACTUAL Level 3D")) Then
Proj.ActiveProject.Tasks.Add RecSet.Fields("Application Name"), iCount
Proj.ActiveProject.Tasks(iCount).Start = "12/31/1997"
Proj.ActiveProject.Tasks(iCount).Finish = "12/31/1999"
If RecSet.Fields("PLANNED Level 3A") <> RecSet.Fields("PLANNED Level
3C") Then
Proj.ActiveProject.Tasks(iCount).Start1 = RecSet.Fields("PLANNED Level
3A")
Proj.ActiveProject.Tasks(iCount).Start2 = RecSet.Fields("PLANNED Level
3C")
End If
If Not RecSet.Fields("ACTUAL Level 3A") = "" Then
Proj.ActiveProject.Tasks(iCount).Finish1 = RecSet.Fields("ACTUAL
Level 3A")
Proj.ActiveProject.Tasks(iCount).Text8 = "ACTUAL 3A"
End If
If Not RecSet.Fields("ACTUAL Level 3C") = "" Then
Proj.ActiveProject.Tasks(iCount).Finish2 = RecSet.Fields("ACTUAL
Level 3C")
Proj.ActiveProject.Tasks(iCount).Text8 = "ACTUAL 3C"
End If
If Not RecSet.Fields("PLANNED Level 3D") = "" Then
Proj.ActiveProject.Tasks(iCount).Start3 = RecSet.Fields("PLANNED
Level 3D")
Proj.ActiveProject.Tasks(iCount).Text8 = "Planned 3D"
End If
Select Case RecSet.Fields("App Criticality")
Case "High"
Proj.ActiveProject.Tasks(iCount).Flag6 = True
Proj.ActiveProject.Tasks(iCount).Text7 = "High"
Case "Medium"
Proj.ActiveProject.Tasks(iCount).Flag1 = "Yes"
Proj.ActiveProject.Tasks(iCount).Text7 = "Medium"
Case "Low"
Proj.ActiveProject.Tasks(iCount).Text7 = "Low"
Proj.ActiveProject.Tasks(iCount).Flag2 = "Yes"
End Select
Select Case RecSet.Fields("Status")
Case "G"
Proj.ActiveProject.Tasks(iCount).Flag3 = "Yes"
Case "Y"
Proj.ActiveProject.Tasks(iCount).Flag4 = "Yes"
Case "R"
Proj.ActiveProject.Tasks(iCount).Flag5 = "Yes"
End Select
If Not RecSet.Fields("Strategy") = "" Then
Proj.ActiveProject.Tasks(iCount).Text2 = RecSet.Fields("Strategy")
iCount = iCount + 1
RecSet.MoveNext
'Else RecSet.MoveNext
End If
Loop
'RecSet.MoveFirst
'If Not RecSet.Fields("") = "" Then Proj.ActiveProject.Tasks(iCount).Text3 =
RecSet.Fields("")
'RecSet.MoveFirst
'For iCount = 1 To Total
'Proj.Application.ActiveProject.Save
Set RecSet = DB.OpenRecordset("P,S Max Dates", DB_OPEN_DYNASET)
RecSet.MoveLast
Total = RecSet.RecordCount
RecSet.MoveFirst
For iCount = 1 To Total
Num = Proj.ActiveProject.Tasks.Count
Boo = False
For jCount = 1 To Num
If Proj.ActiveProject.Tasks(Num).Name = RecSet.Fields("App") Then
Boo = True
Exit For
End If
Next
If Boo Then
Proj.ActiveProject.Tasks(CInt(Num)).Start4 = RecSet.Fields("MinOfMAD
Planned")
Proj.ActiveProject.Tasks(CInt(Num)).Finish4 = RecSet.Fields("MaxOfMAD
Planned")
Proj.ActiveProject.Tasks(Num).Text6 = "Middleware"
RecSet.MoveNext
Else: RecSet.MoveNext
End If
Next
'Test = "taskID:=6, ganttStyle:=13, middleColor:=pjMaroon"
'Proj.GanttBarFormat Test
Set Proj = Nothing
'Proj.Application.FileSaveAs (name="H:\WORK\ACCESS\RMPROJ.MPP")
'Proj.FileOpen "H:\WORK\ACCESS\RMPROJ.MPP"
'Proj.FileNew
'Set Proj1 = Proj.Application
'Set Proj2 = Proj1.Projects("RMPROJ.MPP")
'Set Tasky = Proj2
'Total = Proj.ActiveProject.Tasks.Count
'For iCount = 1 To Total
' Set Tasky = Proj.ActiveProject.Tasks(iCount)
' Tasky.EditDelete
' 'Proj.ActiveProject.SelectRow
' 'Proj.EditDelete
'Next
End Function
Quote:
> I have been asked to look into developing a gantt chart type of capability
> to present a timeline of events similiar to one of those "history of the
> world" charts. I need to allow timeline forks and cause & effect
> considerations. I was thinking of some combination of MS Project and MS
> Access bound together or simply Access with some type of GANTT chart type of
> report. Are there any such capabilities available in Access or 3rd party
> add-ons?
> don
GANTT Charts