Utilizing ODBC to Integrate Microsoft Project with Microsoft Access

Presented by: Johnny Johnson

Mr. Johnson is an integration consultant for Micro-Frame Technologies, Inc, and President of Silverdale Software Systems, Inc. He has taught computer science at the US Naval Academy and business management courses at community colleges. He has developed large scale project management and database systems for the FAA, Air Force, Martin Marietta, as well as other government and commercial customers. He is a Microsoft Project Solutions Partner and Trainer.

Phone: (206) 598-5842
Fax: (206) 598-5843
CompuServe: 74415,1237
Internet: silvrsof@interserv.com

Microsoft Project 4.0 and ODBC Databases

Why Integrate Project Management Data with a Relational Database Management System?

Since the CPM schedule and resource data is only part of the management problem, I almost always find that data from traditional project management systems needs to be integrated with accounting, text documents, pictures, charts, and other organizational data. This is where I find that a relational database environment is needed. Microsoft has provided this in Microsoft Access, Microsoft FoxPro, Visual Basic, SQL Server, and Visual Basic for Applications. Using these tools, we can create an integrated, distributed project management environment.

In Project Management integration, I see the relational database system as the central point of access to the data and to other software because:

I would like to concentrate this discussion on using Microsoft Access as the example database, knowing that there are other products that could also do the job. I particularly like to use Microsoft Access because of its ability to "access" other databases without importing data but by attaching to the database files via "ODBC" and using their data. We can still be maintain any current system by using the original database language then using Microsoft Access to create reports or even update data across several dissimilar databases. Additionally, we can liberally use Microsoft Visual Basic 4.0 and Visual Basic for Applications to create functions, forms, and add functionality.

Organizational Implications of a database of Project Management data

I would like to discuss the organizational and philosophical implications of integrating Project Management data with database systems.

The first questions we should answer are:

  1. Who needs to see which data elements?
  2. Engineers at the detail level need more information than the supervisor or the supervisor's manager. This is always a hard question to answer because it involves organizational attitude about "Micro" management or to what level do we roll up data at each management level. Generally speaking, the less data depicted the better. You can always get detail data when needed. If all management needs, is to answer the question - "When will it be ready?", then just let them see completion dates (scheduled finish, early finish, or late finish data elements).

  3. How often is data updated? By whom? Do we need "real-time" or periodic update?
  4. Many times I have seen organizations that say they must have "real-time" updates, yet they only update data weekly and report on it monthly. The costs in hardware, development, and security may be an unnecessary expense. The entire system development approach depends on how this question is answered.

  5. When is it needed or how often are reports due?
  6. What is the reporting cycle or when do reports have to be presented? Are they due daily, weekly, bi-monthly, monthly?

  7. What form do the data elements need to be viewed?
  8. What is the best way to display the data? (graphical, bar chart, tabular, Gantt, Pert, etc.) Project schedules may make more sense displayed as Gantt Charts but at times a tabular list of finish dates may be more appropriate.

  9. To what level of detail do I need to manage (Managing to Your Threshold of Pain)
  10. If I collect schedule data by the minute, that means my schedule could change every minute, and would require status to be updated every minute or my schedule would be wrong. This is appropriate if you are launching a space shuttle, but for most companies, trying to plan to that level is "cruel and unusual punishment" and generally not necessary. It has been my experience, that very little takes less than four hours, considering telephone calls, coffee, impromptu meetings, and other interruptions. I try to factor these considerations in when I estimate duration's of work. In some instances, if work duration's are very critical, some form of risk analysis could be used to define worst, best, and most likely duration's for a piece of work. Remember, data systems are supposed to save time but they don't guarantee that you will. If you find engineers spending more time entering or manipulating data, they are most likely spending less time on engineering, which is their primary function.

  11. What data elements need to viewed together and where do those elements currently exist. (in other systems, on paper, in someone's head)
  12. Is there significant data existing some where else? Can I attach or import that data? If I can, which data elements do I need? The trick here is to reduce data redundancy, keep data integrity, and keep down development costs. This is where Access's ability to attach to data is especially useful.

Specific Approaches to Integrating Microsoft Project Data with Microsoft Access Data

Microsoft Project

Microsoft Access / ODBC Database

Microsoft Project to/from Microsoft Access Using the "Save to Database" Feature.

With Microsoft Project 4.1, you may save a project to a Microsoft Access or ODBC database directly. You may have it automatically create a "new" database with the correct "project" structure or append or update a previously created "project" database. The Microsoft Project data transferred is captured in four Microsoft Access tables including: Tasks, Assignments, Resources (Pool), and the Project Header data such as Project Name, Manager, Company, and so on. Basically, all data needed for a project is transferred.

Details about the "Save to ODBC Database" Utility

Before you save a project using the "Save to Database" utility, you need to set up a DSN (Data Source Name). This is done in the using the Control Panel's 32 Bit ODBC dialog. You can set up a Microsoft Project database container as follows:

Click on the appropriate Data Source Driver, then Setup

If you are creating a new database, click "create" and give the database a name, else use the "select" option.

Now you are ready to save to that data source. When saving a project to an ODBC database for the first time, you are prompted to select the appropriate data source name (DSN) to which to save the project. The project data will be appended to that database. If that project already exists in the database, it will delete the previous project data and replace it with the new data. Once you have saved a project to a database the first time, the project remembers where it was saved. The next time you save the project, using the "Save to Database" utility, it will automatically be saved to the database where it was last saved. Each project file holds this intelligence in the Text 1-4 fields of Task 0 (the Project Summary task).

Limitations to the Save to ODBC Database Utility

The ODBC "Project Database"

The specifics of the Microsoft Access "project" database structure allow for the user to relate all data.

All table records are "keyed" to a unique Project Key. A sample of the "project" table data structure definition follows:

This is a partial listing of the "Project" table definition in Microsoft Access—fields unique to each project saved in the database.

  1. Relational Queries are the basis of Microsoft Project data in the Microsoft Access Database
  2. Relational reports can be produced by linking the Microsoft Project "key" fields such as "ID" or "UniqueID," along with the Project Key field. The following Microsoft Access Query typifies what one would need for a "one-to-many-to-many" type of relationship. Each project has many tasks, which in turn have many resources. It is important to note that the project key is very important when grouping tasks and resources by project. In the example query that follows, you will see that without the "ProjectKey," one could not tell which tasks or resources belong to which project.

  3. Query linking all of the "Project" database tables together.
  4. Results of Previous Query
  5. Cross project (multiple) project analysis and reporting is possible in the relational environment. The picture below shows a very simple report using the previous query. The report relates Resources to Tasks To Projects.
  6. Simple Relational Report Using Microsoft Project Data - Report Filtered on Carpenter

  7. Reports can be created using other database data in Microsoft Access or by attaching to other ODBC databases for "joined" reporting.
  8. You can update data in Microsoft Access, then send data back to Microsoft Project 4.1 for re-calculation. (This must be done carefully when changing the "actual" values. For example, Scheduled data must be set to Actual data when it is equal or exceeds Scheduled values.)
  9. While the project data is in the database, you have complete multi-user access to the project schedules and can create a system in Microsoft Access including security, forms, reports, and so on.

Processing "Projects" Stored in Microsoft Access
Database

In Microsoft Project 4.1, you may "Open" projects directly from the Microsoft Access or ODBC "project" database. Since all project data is "saved" to the database, you can re-open a project at any time; you don't need to save the ".MPP" file unless you need to for local reasons. After Microsoft Project 4.1 re-calculates the data, changes the logic, and so on, the project can be "saved" back to the original database, thus updating the "master" project database.

Possible Integration Scenarios

There are two scenarios that come immediately to mind.

  1. first is to store all project data ONLY in Microsoft Access or ODBC database. After Microsoft Project 4.1 creates schedules, save all projects into the "project" database and do not save or maintain the "*.MPP" files.
  2. The second way is to store Microsoft Project 4.1 data in the Microsoft Access or ODBC "project" database ONLY for reporting purposes. All changes are maintained in the Microsoft Project 4.0 files (*.MPP") locally. This data is then saved periodically to the "project" database.

Both of these scenarios assume that everyone involved in updating a specific schedule are using the same calendar; otherwise the calculations of schedule dates will vary.

As in all distributed multi-user implementations, contention is an issue. Who has a "copy" of the project from the database on a local machine? If more than one person has a copy, there is a possibility of one person over-writing anther person's data in the database. These issues can be taken care of through policy, security, or an automated "check-out" procedure built into the ODBC database system.

The Tech ED 96 Demo Database

The demo database, MFTITE96.MDB contains all of the demos shown and many sample forms, queries, and reports. Once you start Microsoft Access 7.0 and open the MFTITE96.MDB, go to the forms tab on the database menu and open "frmDemoMainMenu" which brings up a form that allows you to easily explore the demo forms, queries and reports. The demo database is intended to show you ways to properly link tables, make complex queries, design simple forms and produce complex reports across all of the projects in the database. The power of this approach is that it requires virtually no programming to create. Obviously, very sophisticated applications can be produced by using Access Basic and Macros.

Microsoft Access 7.0 Database Window

Demo Database "MFTITE96" Main Menu

Finding Schedule Data Across All Projects

The purpose of the Project Summary form is to demonstrate creating a main and subform linking them together with the ProjectKey, which is created when a Microsoft Project 4.1 project is saved to the database, using the "Save to Database" utility. The main form browses the "Project" table, which has one summary record for each project in the database. The subform, which is linked by ProjectKey, lists all tasks associated with the current Project selected in the main form. Using a Main/Subform metaphor is natural because of the one-to-many relationships that occur in project management models. Users may quickly browse the schedule data across all projects and need not go back to Microsoft Project 4.1 to view this data. Remember, the advantage of using Microsoft Access and Microsoft Project together is that every one can view project management data without having to be a project manager or have training in project management.

The Project Summary Form

Selecting Like Tasks Across All Projects

There are times when you want to compare the same task across all projects. The "Select Specific Task" form demonstrates selecting a unique task name from a list box, then displaying all matching tasks across all projects. In this case the main form is based on a list of unique tasks. When you select a task, the subform, which is linked by the Task Name to the list box control name (the selected task), the subform updates the data to match the selected task.

Microsoft Access Form Design Mode Properties Dialog

The Select Tasks Form

Finding What Resources Are Assigned to a Specific Task

To answer the question, "Who is working on a specific task on a specific project?" use the "Show Resources Assigned to Task" form. Again this form uses a main/subform construct linking this time on both ProjectKey and Task Name to select the subform data. Although it might be faster to link on TaskID, there is no guarantee that the same task name will have the same TaskID accross several projects. In Microsoft Project 4.0, it is very difficult to ensure the same TaskID across like work. If this is necessary, it might be better to create a standardized coding scheme and use one of the user text fields (Text1-Text10) and assign it there.

Find Resources Assigned to a Specific Task and Project

In this case the values returned by the list box controls are used to link to the subform. SelectedTask and SelectedProject are the names of the list box controls and assume the value returned for the bound column of the combo box.

Control Names Used to Link Main to Subform

What Tasks Are Assigned to a Specific Resource

Probably the most complex query you can make, using the four basic tables in the project database, is to find out which resources are assigned to which tasks on which projects. It is the one-to-many-to-many that makes this query interesting. The demo form allows you to select a unique resource name then links to the subform query by resource name. You cannot use resource ID's because you cannot guarantee that the resource pool across all projects is consistent. Each project, when is saved to the database, gets its own copy of the resource table (pool). There are ways of maintaining a common pool but it requires that you set up the system and procedures up front. In other words, set up and ensure that everyone uses the common resource pool from the outset.

All Tasks Assigned to Carpenter Across All Projects

Sending Data to Microsoft Project to Produce Gantt Charts

Clicking on the "Send Resource Schedule to Project" will take the filtered data and send it via DAO to Microsoft Project and produce a new project from that data.

Code behind the button:

Sub btnSendSchedToProject_Click()
On Error GoTo Err_btnSendSchedToProject_Click
SendFilteredTasksToMSP41
Exit_btnSendSchedToProject_Click:
    Exit Sub
Err_btnSendSchedToProject_Click:
    MsgBox Err.Description
    Resume Exit_btnSendSchedToProject_Click
End Sub

The Module Sub routine:

Public Sub SendFilteredTasksToMSP41()
Dim dbs As Database, MyQuery As QueryDef, MyRS As Recordset
Dim oProj As Object, ResourceToSend As String
Dim dbLocal As Database
Dim tmpID As Long
' Return Database variable that points to current database.
Set dbLocal = CurrentDb
ResourceToSend = Forms![frmTasksAssignedToResource]![SelectedResource]
MsgBox ResourceToSend
' Construct SQL statement including parameters.
Set MyQuery = dbLocal.OpenQueryDef("qryFilteredTasks")
MyQuery.SQL = "SELECT * FROM qryResourcesToProject WHERE [Resources].[Name] = """ & _
                 ResourceToSend & """ ;"
Set MyRS = dbLocal.OpenRecordset("qryFilteredTasks", dbOpenDynaset)
'create an instance of Project
Set oProj = CreateObject(Class:="MSProject.Application.4_1")
With oProj
    .DisplayAlerts = False
    .FileNew
End With
'increment the counter for the Task ID
tmpID = 1
Do Until MyRS.EOF
    'use the settaskfield method to write data from Access     'record to Project
    With oProj
    .SetTaskField Field:="Name", Value:=MyRS![Tasks.Name],                 TaskID:=tmpID
    .SetTaskField Field:="Start", Value:=MyRS![Start],                     TaskID:=tmpID
    .SetTaskField Field:="Duration", Value:=MyRS![Duration],                 TaskID:=tmpID
    .SetTaskField Field:="Resource Names",                                 Value:=MyRS![Resources.Name], TaskID:=tmpID
    End With
    MyRS.MoveNext
    tmpID = tmpID + 1
Loop
'Make project visible
With oProj
    .Visible = True
    .DisplayAlerts = True
End With
End Sub

The Resulting Gantt Chart:

Gantt Chart of all of the Carpenter's work.

Demo Reports

The demo reports included in the database demonstrate how to create the underlying queries and group correctly to get resource and task data. The Resources by Task report groups on Project.Name and Task.Name and is sorted by early task start date as follows:

Sorting and Grouping of Resources by Task

Report Design Mode of Resources by Task Report

Totaling in a report can be done in the footer of the group you are totaling. In this case, I totaled the resource cost for each task by creating a text box with its control source equal to =Sum([Cost]) in the tasks footer. To get the total for the project, just duplicate the text box in the project footer and the total of all task costs gets added there.

Total a Field Using a Text Box

Resulting Resource by Task Report

The Tasks Assigned to a Specific Resource Report

This report is less complex than the last report because it only groups on resource name.

Tasks Assigned to a Specific Resource

The Classic "Look Ahead" Report

The "Soon to Come Due 30/60 report uses a calculated field in the query to make the report group on tasks due from today + 30 days and tasks due 30-60 days from now. This technique makes "look ahead" reports very easy. First of all, the Due3060 query limits all tasks to those scheduled to finish from "Now" until 60 days from now. To do this, simply enter the Finish field's criteria as:

>Now() And <Now()+60

The DUE3060 Query - Design View

The trick in this query is to create a calculated field called "Due" then make it equal to a Boolean expression as follows, by Selecting Shift-F2 to zoom in so that it is easier to type in the expression. Basically, if a finish date falls between now and 30 days from now, Due is equal to "30" and if the finish falls between 30 to 60 days from now, Due is equal to "30 to 60". Now in the report group on "Due" and sort by ascending finish date.

Boolean Expression that Creates Virtual Field "Due"

Results of Due3060 Report * Note the Grouping Headers print out correctly with the value set in the calculated Due field in the query.

Conclusion

The purpose of this presentation was to show that by using both Microsoft Project 4.1 and an ODBC database together, one can not only create integrated applications but also make project management data available to a broad base of users without the overhead of project management training those who need only view the data. Additionally, I hope you can see the advantages from both a system and organizational perspective of using Microsoft Project 4.0 in large scale implementations.