We have this idea that presenting HTML pages to update a database with basic schedule info, then transferring that data to MS Project might be easier than making everyone buy Project and learn how to use it. Only the Project Manager will use Project for detailed schedule stuff. Others will get basic schedule and project data from database reports.
We're using MySQL and C code with HTML, Perl, and other scripts as needed. We think we'll have to go through Access to use a VB module to export to MS Project to update an .mpp. Or can it be done directly? If so, how? And, I really do need code examples.
Also, I really need some VB code examples for updating an .mpp file from Access. I've got the short module for adding tasks, but I can't (thanks for the great, intuitive help file, Microsoft) figure out the simplest task to change the StartDate, duration, and other fields for the inserted Tasks. Can someone send examples?
One other thing, we'll probably want the PM to be able to tranfer data from the master project schedule to the MySQL database by selecting update from an HTML button. Any ideas there?
I have worked with Access & MS Project, here are a few rambling
thoughts. You won't find much info out there on this topic.
1) Users develop and maintain their plans in Project
using as many levels as desired. Resources are assigned
as well as Travel and Material costs.
2) The plans are submitted/resubmitted to the Access
database which models their geometry and resource
assignments. Access checks to make sure that Actual
work efforts are not deleted from the MS Project
representation ... it will reject the resubmittal.
3) Access collects actual costs and provides most of
the reports, in conjunction with Excel.
4) Access does not send the actual costs back to Project
because it thoroughly destroys their perception of their
plan.
5) Other than maintaining their plans, Project is mainly
useful for examining resources allocation accross the
many projects. (Consolidating projects)
6) Project can't be trusted to maintain any Actual
financial data because users have total control over their
plan and can delete what they want.
7) Project is a single-user maintained application and
can't be shared in a read/write mode.
Here is a little bit of code that reads in a project.
Somewhere I have code for writing a project ... I'll look.
Note: You must use the Object Model, Project can save
as an Access DB, but DON'T mess with the tables!
Code:
' ****************************************************
Dim varFileNames As Variant
Dim app As Object
Dim prj As MSProject.Project
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim i As Integer
Dim strFileName As String
Dim strArcFileName As String
Dim strArchivePath As String
Dim strSrcFileSpec As String
Dim strArcFileSpec As String
Dim strArcDateTime As String
Dim fSaveSuccess As Boolean
Dim intSuccesses As Integer
Dim strMsg As String
Set app = CreateObject("MSProject.Application.9")
app.Alerts False
app.Visible = False
app.FileOpen Name:=varFileNames(i)(1), ReadOnly:=False
Set prj = app.Projects(varFileNames(i)(1))
'
' process all the tasks in the project
'
For i = 0 To prj.Tasks.Count
If i = 0 Then
Set tsk = prj.ProjectSummaryTask
curBaseCost = tsk.FixedCost
If curBaseCost = 0 Then
fLevelZeroIPT = False
Else
fLevelZeroIPT = True
Call SaveIptTaxAmount(rstPrj, curBaseCost, tsk.UniqueID)
End If
Else
Set tsk = prj.Tasks(i)
End If
If tsk Is Nothing Then
' skip blank row
Else
'
' set a flag whether or not the task is to be rebaselined
'
Select Case intSaveOption
Case BASELINE_ALL
' first-time approval (aka auto-baseline)
fBaseline = True
Case BASELINE_NONE
' resubmittal with rebaseline declined
fBaseline = False
Case BASELINE_SELECTED
' resubmittal with partial rebaselining
' on a product-by-product basis
If tsk.OutlineLevel = 1 Then
rst.FindFirst "task_name='" & tsk.Name & "'"
If rst.NoMatch Then
' the product is new to the project, so this is its first-time approval
' even though the project itself has been approved before
fBaseline = True
Else
' the product exists from prior approvals, so rebaseline or not
' according to what the user specified
fBaseline = rst!flag
End If
Else
' use the value of fBaseline that was used for the previous task;
' this causes children task to be rebaselined (or not) the same as their level 1 parent
fBaseline = fBaseline
End If
End Select
'
' find the record for the current task (create one, if necessary)
'
lngTaskKey = TaskKey(lngProjUID, tsk.UniqueID)
Do
rstTsk.FindFirst "task_key=" & lngTaskKey & ""
If rstTsk.NoMatch Then
rstTsk.AddNew
rstTsk!task_key = lngTaskKey
rstTsk!PROJ_UID = lngProjUID
rstTsk!task_uid = tsk.UniqueID
rstTsk.Update
End If
Loop Until Not rstTsk.NoMatch
'
' capture information about the task
'
rstTsk.Edit
rstTsk!task_id = tsk.id
rstTsk!TASK_NAME = tsk.Name
rstTsk!task_outline_level = tsk.OutlineLevel
rstTsk!task_outline_num = tsk.OutlineNumber
If i = 0 Then
rstTsk!task_parent_uid = -1
Else
Set tskParent = tsk.OutlineParent
rstTsk!task_parent_uid = tskParent.UniqueID
End If
rstTsk!task_est_start = tsk.Start
rstTsk!task_est_finish = tsk.Finish
rstTsk!task_matl_cost = tsk.Cost1
rstTsk!task_trav_cost = tsk.Cost2
rstTsk!task_wssl_cost = tsk.Cost3
rstTsk!task_labor_cost = 0
If fBaseline Then
rstTsk!task_base_start = tsk.Start
rstTsk!task_base_finish = tsk.Finish
rstTsk!task_base_cost = CLng(tsk.Cost * 100) / 100
End If
rstTsk.Update
'
' pay particular attention to product-level tasks
'
If tsk.OutlineLevel = 1 Then
' store IPT tax info, if applicable
If InStr(1, tsk.Name, "IPT") And (tsk.FixedCost > 0) And Not fLevelZeroIPT Then
Call SaveIptTaxAmount(rstPrj, tsk.FixedCost, tsk.UniqueID)
End If
' adjustment (but don't store yet) the project-level baseline values, if applicable
curBaseCost = curBaseCost + rstTsk!task_base_cost
If IsEmpty(varBaseStart) Or (rstTsk!task_base_start < varBaseStart) Then
varBaseStart = rstTsk!task_base_start
End If
If IsEmpty(varBaseFinish) Or (rstTsk!task_base_finish > varBaseFinish) Then
varBaseFinish = rstTsk!task_base_finish
End If
End If
'
' capture information about the task's assignments
'
For Each asn In tsk.Assignments
rstRes.FindFirst "res_name='" & asn.ResourceName & "'"
If rstRes.NoMatch Then
' resource is not a member of the master resource list
' so we'll ignore the assignment
Else
strResKey = rstRes!res_key
rstAsn.FindFirst "task_key=" & TaskKey(lngProjUID, asn.TaskUniqueID) & " AND RES_KEY='" & strResKey & "'"
If rstAsn.NoMatch Then
' assignment is a new one, so add it to the database
rstAsn.AddNew
rstAsn!task_key = (lngProjUID * 10 ^ 6) + asn.TaskUniqueID
rstAsn!res_key = strResKey
Else
' assignment already exists, so edit it
' and remove it from the assignments collection
' so that we'll know it has been updated
rstAsn.Edit
clcAsn.Remove (CStr(rstAsn!task_key) & rstAsn!res_key)
End If
rstAsn!assn_est_start = asn.Start
rstAsn!assn_est_finish = asn.Finish
rstAsn!assn_work = asn.Work / 60
rstAsn.Update
rstTsk.Edit
rstTsk!task_labor_cost = rstTsk!task_labor_cost + asn.Cost
rstTsk.Update
End If
Next
'
' allow the operating system to process its message queue
'
DoEvents
End If
frm.SetProgress i + 1
DoEvents
Next
app.Quit
Set app = Nothing
' ****************************************************
Thanks, so much! This will help immensely. As I played with VB, I did manage to find the method and objects to update Project from VB in Access. We are just beginning, so I didn't do anything but realize how it is done. These code examples will help, for sure.
Our first task is just to take a number of schedule maintained by different offices and identify gaps and redundancies in delivery dates for applications' functions. Supposedly, these schedule represent an "integrated master schedule", but in reality the owners have not done much cross fertilization. So we will take what we need into Access, then to MySQL, for our analysts to begin finding gaps, etcs. So, just basic views are needed. All we have to deliver is another basic schedule and document that identifies these gaps.
What we hope to deliver is a database view of all the schedules with triggers to help managers understand when their entries impact other projects. They are supposed to be merging project into "service-based" programs, but still need a macro view of what must be migrated from legacy systems and help to ensure they don't duplicate functionality within their services. The database will help them see a complete view and then we'll let them select what they need to export to Project or import from Project....we hope.
So, next step is to set up myODBC. Have only scanned the documentation. Are you aware of any pitfalls? Does myODBC allow data transfer both ways, or is it used only to read mySQL into Access?
Your code helped a lot. I'm getting there. I've converted to ADO, but still have questions.
-- When I OpenFile ("project.mpp") it actually opens the project file in Project in an open window. I have to close Project to have the code continue.
-- I get the prj.Tasks.Count displayed in a msg box, but when I try to read the task with rst!task = prj.name it tells me the server is not found.
Actually, I'm trying to remember the code while at home. I'll get the lines to you tomorrow at work.
You don't have to Close the Project window in order for the
code to run.
I'll try to find some sample code for writing to Project from
Access. (Old code, as we quit updating Project a while ago,
it was not worth the trouble).
Updating project is a maybe. Mostly, I have about 80 project files to suck into Access to facilitate a single view for analysis of gaps and duplication of effort. There are only a few of Project's fields to import, but I want to do it with VB to avoid having to map export from Project so many times. Plus, we have to do a periodic pull since we don't have control of the .mpp files. And the good news is that I have succeeded. Whoopee! You code was invaluable, but getting from DAO to ADO was a hurdle since I'm a rookie.
Now, the problems are that every Project file opens, and I get the Alert for 'Enable/Disable Macros'. I did comment out the Alert = False line, so have to put that back in. But, Visible is set False, so I would expect to not see the application pop up. Also, there must be a SQL Server connection set up in one file because after about 50 files I was asked to log into a server. Maybe setting Alerts to False will kill that. If not, please let me know how to get rid of this aggravation.
Putting the data back into Project should not be a problem. We're not doing duration, cost, and other complicated fields.
So, I think minor things remain, if you can help. Eventually, we'd like to take the Project data directly to MySQL and back, if you have any knowledge of that.
Well, managed to do it. I guess I turned off alerts and visible correctly so all 85 .mpp files imported with no problems after I got the correct field sizes in Access. Only problems was that a few tasks had external links that insisted on trying to connect as they were read into Access. I just had to cancel the request, is all.
One would think that someone, someplace would have written an ICD for this kind of thing. Maybe I will. Think it would be useful? Actually, I have very limited knowledge of the fields and formats, so would have to do a lot of experimenting. How would I find out, for example, which fields in Project are calculated, so unavailable for export?
I may still have some questions about export from Access back to Project. Still need to figure out how to do away with the middle-man (Access) and go back and forth from MySQL.
