none
Add data to the Task Usage sheet from my MS ACCESS Database RRS feed

  • Question

  • Hello

    I have created 4 tables in MS ACCESS where

    The first table contains a list of projets and the project id

    The second table has resources and rates

    The third table has assignment of resources to each of these projects.

    I have loaded each of the projects as a task in MS Project and loaded the resources with the rates in resource sheet and used the assignment table and assigned all the projects to the resources.

    The fourth table is the timephase table which has every resource linked with each of the task and their time by weeks in the table.

    Project ID Project Name GL Year GL Month Resource Name Start_Date Finish_Date Total Hours
    KT00410 Project Digital 2014 1 SPENCER L 05-Jan-14 11-Jan-14 1
    KT00410 Project Digital 2014 1 SPENCER L 12-Jan-14 18-Jan-14 3
    KT00410 Project Digital 2014 2 SPENCER L 19-Jan-14 25-Jan-14 1

    How do i enter this data in the Task Usage sheet. 

    Thanks

    Ram

    This is the code i have so far

    'Create Connection to the .mdb file

    With Files

    .DialogTitle = "Please Find .mdb file"

    .Filter = "Access Files|*.accdb"

    Files.ShowOpen

    Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _

    & Files.FileName & ";Persist Security Info=False"

    Conn.Open

    End With

    'Setup up new Project file

    Application.FileNew

    'Import all Tasks

    ' If a Task has a Predecessor ignore its dates

    Set rs = New ADODB.Recordset

    rs.Open "SELECT * FROM Task_Table", Conn

    'ActiveProject.ProjectStart = rs![Start Date]

    FirstTask = True

    Do Until rs.EOF

    Set Tsk = ActiveProject.Tasks.Add(CStr(rs![ABC Name]))

    Tsk.Text1 = rs.Fields(0).Value

    If FirstTask Then 'If first Task, do nothing

    FirstTask = False

    Else

    End If

    rs.MoveNext

    Loop

    rs.Close

    'Import all Resources

    rs.Open "SELECT * FROM Resource_Table ORDER BY [Resource_Name]", Conn

    Do Until rs.EOF

    Set Res = ActiveProject.Resources.Add(CStr(rs![Resource_Name]))

    Res.StandardRate = rs.Fields(1).Value

    rs.MoveNext

    Loop

    rs.Close

    'Make Assignments of Resources on Tasks

    rs.Open "SELECT * FROM Assignment_Tbl ORDER BY [ABC Name]", Conn

    Do Until rs.EOF

    Set Tsk = ActiveProject.Tasks(CStr(rs![ABC Name]))

    Set Res = ActiveProject.Resources(CStr(rs![Res_Name]))

    'Units = rs!Units

    Set Ass = Tsk.Assignments.Add(ResourceID:=Res.ID)

    rs.MoveNext

    Loop

    rs.Close

    MsgBox "Yay I am done", vbOKOnly

    'Tidy up

    Set rs = Nothing

    Conn.Close

    Set Conn = Nothing

    End Sub


    R

    Wednesday, April 9, 2014 10:19 PM

All replies