Microsoft Project OLE DB provider limitations
Accessing the OLE DB table structure using data access pages in Microsoft Access
Sample code using ActiveX Data Objects (ADO)
Accessing the provider on your computer
Accessing the provider on a Microsoft Project Central server
Tables
Project
Tasks
Resources
Assignments
Successors
Predecessors
TaskSplits
BaselineTaskSplits
Calendars
CustomOutlineCodeLookupTables
This document is intended to provide the information necessary to access Microsoft Project data through the Microsoft Project 9.0 OLE DB Provider. In addition to describing the OLE DB table structures, this document details some of the limitations and idiosyncrasies of the provider, how to access the table structure using data access pages, and sample ActiveX Data Objects (ADO) code.
There are a few aspects of the OLE DB provider for Microsoft Project that are unique and should be noted to prevent unexpected results:
Additionally, the provider supports three registry keys that determine the number of seconds that must elapse before certain time-out conditions occur. These keys can be found under the HKEY_LOCAL_MACHINE\Software\Microsoft\Office\9.0\MS Project\OLE DB Provider subkey of the registry:
Registry Key | Default Value | Description |
---|---|---|
TimeoutOnLoad | 90 | Determines how long the provider attempts to load a project before returning an error message that it is unavailable. |
TimeBeforeUnload | 600 | Determines how long a project remains open after another project is loaded. Until a new project is loaded, the current project remains in memory, regardless of this setting. |
TimeBeforeRefresh | 1 | Determines how often the current project is checked for updated information. |
Tip Setting a key to 0 prevents any time-out.
Note Before you edit the registry, make sure you understand how to restore it if a problem occurs. Editing the registry incorrectly can cause serious problems that may require you to reinstall your operating system; therefore, edit the registry at your own risk.
The current implementation of the provider has a few limitations, as follows:
Data access pages in Microsoft Access provide a versatile and powerful method for generating reports using data from Microsoft Project. They also provide a convenient way to view Microsoft Project's OLE DB table structure. For more information on data access pages, see Microsoft Access 2000 Help.
Note Data access pages require Microsoft Internet Explorer 5.
The following steps describe how to connect to a project using Microsoft Project's OLE DB provider.1 | Start Microsoft Access 2000 and create a new database by clicking Access database wizards, pages, and projects. | |
2 | Click the General tab of the New dialog box, and then double-click Data Access Page. | |
3 | Click Design View on the New Data Access Page dialog box. | |
4 | Click the Provider tab of the Data Link Properties dialog box, and then click Microsoft Project 9.0 OLE DB Provider. | |
5 | Click the All tab, click Project Name, and then click Edit Value. (To connect to a Microsoft Project database file, be sure to enter values for Data Source, Initial Catalog, User ID, and, if necessary, Password, instead of Project Name.) | |
6 | Enter the path and file name of the project you want to access. | |
7 | Click OK to close the Data Link Properties dialog box.
The OLE DB table structure of the project displays in the Field List dialog box. |
ADO provides simple access to the OLE DB interface through a set of objects, events, methods, and properties. Likely scenarios for ADO operations include accessing the provider on your computer and accessing it on a Microsoft Project Central server.
This sample accesses a file on your computer and displays some assignment information from the project.
Note For the sample to compile, you must add a reference to the Microsoft ActiveX Data Objects 2.1 Library to your project. For more information, see the topic "Set a Reference to a Type Library" in Visual Basic How-To Topics Help.
Sub ConnectLocally()
Dim conData As New ADODB.Connection
Dim rstAssigns As New ADODB.Recordset
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String
conData.ConnectionString = "Provider=Microsoft.Project.OLEDB.9.0;PROJECT NAME=" & FILE_NAME
' To connect to a Microsoft SQL Server or Oracle Server file, you must also supply User ID and Password arguments
' conData.ConnectionString = "Provider=Microsoft.Project.OLEDB.9.0;User ID=jsmith;Password=MyPass5;PROJECT NAME=" & FILE_NAME
conData.ConnectionTimeout = 30
conData.Open
strSelect = "SELECT ResourceUniqueID, AssignmentResourceID, AssignmentResourceName, TaskUniqueID, AssignmentTaskID," & _
& " AssignmentTaskName FROM Assignments WHERE TaskUniqueID > 0 ORDER BY AssignmentTaskID ASC"
rstAssigns.Open strSelect, conData
Do While Not rstAssigns.EOF
For intCount = 0 To rstAssigns.Fields.Count - 1
strResults = strResults & "'" & rstAssigns.Fields(intCount).Name & "'" & _
Space(30 - Len(rstAssigns.Fields(intCount).Name)) & vbTab & _
CStr(rstAssigns.Fields(intCount).Value) & vbCrLf
Next
strResults = strResults & vbCrLf
rstAssigns.MoveNext
Loop
conData.Close
Open "C:\My Documents\Results.txt" For Output As #1
Print #1, strResults
Close #1
Shell "Notepad C:\My Documents\Results.txt", vbMaximizedFocus
End Sub
This sample loads a file from a network server into the provider on a Microsoft Project Central server and displays some task information from the project.
Note For the sample to compile, you must add references to the Microsoft Remote Data Services 2.1 Library and the Microsoft ActiveX Data Objects 2.1 Library to your project. For more information, see the topic "Set a Reference to a Type Library" in Visual Basic How-To Topics Help.
Const FILE_NAME = "\\accounting\files\MyProject.mpp"
Const PC_SERVER = "http://corp1"
Sub ConnectToServer()
Dim dbsData As New RDS.DataControl
Dim rstTasks As ADODB.Recordset
Dim intCount As Integer
Dim strResults As String
dbsData.Connect = "Remote Server=" & PC_SERVER & ";Remote Provider=Microsoft.Project.OLEDB.9.0;PROJECT NAME=" & FILE_NAME
dbsData.Server = PC_SERVER
dbsData.ExecuteOptions = RDS.adcExecSync
dbsData.SQL = "SELECT TaskID, TaskName FROM Tasks WHERE TaskUniqueID > 0"
dbsData.Refresh
Set rstTasks = dbsData.Recordset
For intCount = 0 To rstTasks.Fields.Count - 1
strResults = strResults & rstTasks.Fields(intCount).Name & Space(30 - Len(rstTasks.Fields(intCount).Name))
Next
strResults = strResults & vbCrLf
Do While Not rstTasks.EOF
For intCount = 0 To rstTasks.Fields.Count - 1
strResults = strResults & CStr(rstTasks.Fields(intCount).Value) & _
Space(30 - Len(CStr(rstTasks.Fields(intCount).Value)))
Next
strResults = strResults & vbCrLf
rstTasks.MoveNext
Loop
rstTasks.Close
Open "C:\My Documents\Results.txt" For Output As #1
Print #1, strResults
Close #1
Shell "Notepad C:\My Documents\Results.txt", vbMaximizedFocus
End Sub
The tables exposed through Microsoft Project's OLE DB provider are: Project, Tasks, Resources, Assignments, Successors, Predecessors, Task Splits, Baseline Task Splits, Calendars, and Custom Outline Code Lookup Tables. The columns (fields) and their data types for each table are described below. Where appropriate, additional details about the table are included.
Note Names that are formatted blue and bold indicate a column (field) common among more than one table.
Provides access to the project-level settings on the Project Information (Project menu), Options (Tools menu), and Properties (File menu) dialog boxes. For the fields of the project summary task, access the Task table using a value of 0 for the TaskID column.
Column Name | Data Type |
---|---|
Project | Text |
ProjectAuthor | Text |
ProjectCalendarName | Text |
ProjectCategory | Text |
ProjectCompany | Text |
ProjectCreationDate | Date |
ProjectCriticalSlackLimit | Number |
ProjectCurrencyDigits | Number |
ProjectCurrencyPosition | Number |
ProjectCurrencySymbol | Text |
ProjectCurrentDate | Date |
ProjectDaysPerMonth | Number |
ProjectDefaultFinishTime | Number |
ProjectDefaultFixedCostAccrual | Boolean |
ProjectDefaultOvertimeRate | Text |
ProjectDefaultStandardRate | Text |
ProjectDefaultStartTime | Number |
ProjectDefaultTaskType | Number |
ProjectDurationFormat | Number |
ProjectEditableActualCosts | Boolean |
ProjectExpandTimephased | Boolean |
ProjectFinishDate | Date |
ProjectFYStart | Date |
ProjectHonorConstraints | Boolean |
ProjectInsertedProjectsLikeSummary | Boolean |
ProjectIsResourcePool | Boolean |
ProjectKeywords | Text |
ProjectLastSaved | Date |
ProjectManager | Text |
ProjectMinsPerDay | Number |
ProjectMinsPerWeek | Number |
ProjectMultipleCriticalPaths | Boolean |
ProjectNewTasksEffortDriven | Boolean |
ProjectNewTasksEstimated | Boolean |
ProjectPoolAttachedTo | Text |
ProjectRevision | Text |
ProjectSavePreviewPicture | Boolean |
ProjectScheduledFromStart | Boolean |
ProjectShowEstimatedDurations | Boolean |
ProjectSplitInProgressTasks | Boolean |
ProjectSpreadActualCosts | Boolean |
ProjectSpreadPercentComplete | Boolean |
ProjectStartDate | Date |
ProjectStatusDate | Date |
ProjectSubject | Text |
ProjectTaskUpdatesResource | Boolean |
ProjectTitle | Text |
ProjectWorkFormat | Number |
Column Name | Data Type |
---|---|
Project | Text |
TaskUniqueID | Number |
TaskActualCost | Number |
TaskActualDuration | Number |
TaskActualFinish | Date |
TaskActualOvertimeCost | Number |
TaskActualOvertimeWork | Number |
TaskActualStartNumber | Number |
TaskACWP | Number |
TaskBaselineCost | Number |
TaskBaselineDuration | Number |
TaskBaselineDurationEstimated | Boolean |
TaskBaselineFinish | Date |
TaskBaselineStart | Date |
TaskBaselineWork | Number |
TaskBCWP | Number |
TaskBCWS | Number |
TaskCalendar | Text |
TaskCompleteThrough | Date |
TaskConfirmed | Boolean |
TaskConstraintDate | Date |
TaskConstraintType | Number |
TaskContact | Text |
TaskCost | Number |
TaskCost1-10 | Number |
TaskCost1-10Indicator | Number |
TaskCostVariance | Number |
TaskCreated | Date |
TaskCritical | Boolean |
TaskCV | Number |
TaskDate1-10 | Date |
TaskDate1-10Indicator | Number |
TaskDeadline | Date |
TaskDuration | Number |
TaskDuration1-10 | Number |
TaskDuration1-10Estimated | Boolean |
TaskDuration1-10Indicator | Number |
TaskDurationVariance | Number |
TaskEarlyFinish | Date |
TaskEarlyStart | Date |
TaskEffortDriven | Boolean |
TaskEstimated | Boolean |
TaskExternalTask | Boolean |
TaskFinish | Date |
TaskFinish1-10 | Date |
TaskFinish1-10Indicator | Number |
TaskFinishSlack | Number |
TaskFinishVariance | Number |
TaskFixedCost | Number |
TaskFixedCostAccrual | Number |
TaskFlag1-20 | Boolean |
TaskFlag1-20Indicator | Number |
TaskFreeSlack | Number |
TaskHideBar | Boolean |
TaskHyperlink | Text |
TaskHyperlinkAddress | Text |
TaskHyperlinkHref | Text |
TaskHyperlinkSubAddress | Text |
TaskID | Number |
TaskIgnoreResourceCalendar | Boolean |
TaskIsNull | Boolean |
TaskLateFinish | Date |
TaskLateStart | Date |
TaskLevelAssignments | Boolean |
TaskLevelingCanSplit | Boolean |
TaskLevelingDelay | Number |
TaskLinkedFields | Boolean |
TaskMarked | Boolean |
TaskMilestone | Boolean |
TaskName | Text |
TaskNotes | Text |
TaskNumber1-20 | Number |
TaskNumber1-20Indicator | Number |
TaskObjects | Number |
TaskOutlineCode1-10 | Text |
TaskOutlineLevel | Number |
TaskOutlineNumber | Text |
TaskOverallocated | Boolean |
TaskOvertimeCost | Number |
TaskOvertimeWork | Number |
TaskPercentComplete | Number |
TaskPercentWorkComplete | Number |
TaskPredecessors | Text |
TaskPreleveledFinish | Date |
TaskPreleveledStart | Date |
TaskPriority | Number |
TaskRecurring | Boolean |
TaskRegularWork | Number |
TaskRemainingCost | Number |
TaskRemainingDuration | Number |
TaskRemainingOvertimeCost | Number |
TaskRemainingOvertimeWork | Number |
TaskRemainingWork | Number |
TaskResourceGroup | Text |
TaskResourceInitials | Text |
TaskResourceNames | Text |
TaskResourcePhonetics | Text |
TaskResponsePending | Boolean |
TaskResume | Date |
TaskRollup | Boolean |
TaskStart | Date |
TaskStart1-10 | Date |
TaskStart1-10Indicator | Number |
TaskStartSlack | Number |
TaskStartVariance | Number |
TaskStop | Date |
TaskSubprojectFile | Text |
TaskSubprojectReadOnly | Boolean |
TaskSuccessors | Text |
TaskSummary | Boolean |
TaskSummaryProgress | Number |
TaskSV | Number |
TaskTeamStatusPending | Boolean |
TaskText1-30 | Text |
TaskText1-30Indicator | Number |
TaskTotalSlack | Number |
TaskType | Number |
TaskUniqueIDPredecessors | Text |
TaskUniqueIDSuccessors | Text |
TaskUpdateNeeded | Boolean |
TaskVAC | Number |
TaskWBS | Text |
TaskWBSPredecessors | Text |
TaskWBSSuccessors | Text |
TaskWork | Number |
TaskWorkVariance | Number |
Work values for material resources are returned in the units defined in the interface, rather than minutes * 1000.
Column Name | Data Type |
---|---|
Project | Text |
ResourceUniqueID | Number |
ResourceAccrueAt | Number |
ResourceActualCost | Number |
ResourceActualOvertimeCost | Number |
ResourceActualOvertimeWork | Number |
ResourceActualWork | Number |
ResourceACWP | Number |
ResourceAvailableFrom | Date |
ResourceAvailableTo | Date |
ResourceBaseCalendar | Text |
ResourceBaselineCost | Number |
ResourceBaselineWork | Number |
ResourceBCWP | Number |
ResourceBCWS | Number |
ResourceCanLevel | Boolean |
ResourceCode | Text |
ResourceConfirmed | Boolean |
ResourceCost | Number |
ResourceCost1-10 | Number |
ResourceCost1-10Indicator | Number |
ResourceCostPerUse | Number |
ResourceCostVariance | Number |
ResourceCV | Number |
ResourceDate1-10 | Date |
ResourceDate1-10Indicator | Number |
ResourceDuration1-10 | Number |
ResourceDuration1-10Indicator | Number |
ResourceEmailAddress | Text |
ResourceFinish | Date |
ResourceFinish1-10 | Date |
ResourceFinish1-10Indicator | Number |
ResourceFlag1-20 | Boolean |
ResourceFlag1-20Indicator | Number |
ResourceGroup | Text |
ResourceHyperlink | Text |
ResourceHyperlinkAddress | Text |
ResourceHyperlinkHref | Text |
ResourceHyperlinkSubAddress | Text |
ResourceID | Number |
ResourceInitials | Text |
ResourceIsNull | Boolean |
ResourceLinkedFields | Boolean |
ResourceMaterialLabel | Text |
ResourceMaxUnits | Number |
ResourceName | Text |
ResourceNotes | Text |
ResourceNTAccount | Text |
ResourceNumber1-20 | Number |
ResourceNumber1-20Indicator | Number |
ResourceObjects | Number |
ResourceOutlineCode1-10 | Text |
ResourceOverallocated | Boolean |
ResourceOvertimeCost | Number |
ResourceOvertimeRate | Text |
ResourceOvertimeWork | Number |
ResourcePeakUnits | Number |
ResourcePercentWorkComplete | Number |
ResourcePhonetics | Text |
ResourceRegularWork | Number |
ResourceRemainingCost | Number |
ResourceRemainingOvertimeCost | Number |
ResourceRemainingOvertimeWork | Number |
ResourceRemainingWork | Number |
ResourceResponsePending | Boolean |
ResourceStandardRate | Text |
ResourceStart | Date |
ResourceStart1-10 | Date |
ResourceStart1-10Indicator | Number |
ResourceSV | Number |
ResourceTeamStatusPending | Boolean |
ResourceText1-30 | Text |
ResourceText1-30Indicator | Number |
ResourceType | Number |
ResourceUpdateNeeded | Boolean |
ResourceVAC | Number |
ResourceWork | Number |
ResourceWorkgroup | Text |
ResourceWorkVariance | Number |
Column Name | Data Type |
---|---|
Project | Text |
ResourceUniqueID | Number |
TaskUniqueID | Number |
AssignmentActualCost | Number |
AssignmentActualFinish | Date |
AssignmentActualOvertimeCost | Number |
AssignmentActualOvertimeWork | Number |
AssignmentActualStart | Date |
AssignmentActualWork | Number |
AssignmentACWP | Number |
AssignmentBaselineCost | Number |
AssignmentBaselineFinish | Date |
AssignmentBaselineStart | Date |
AssignmentBaselineWork | Number |
AssignmentBCWP | Number |
AssignmentBCWS | Number |
AssignmentConfirmed | Boolean |
AssignmentCost | Number |
AssignmentCost1-10 | Number |
CostRateTable | Number |
AssignmentCostVariance | Number |
AssignmentCV | Number |
AssignmentDate1-10 | Date |
AssignmentDelay | Number |
AssignmentDuration1-10 | Number |
AssignmentFinish | Date |
AssignmentFinish1-10 | Date |
AssignmentFinishVariance | Number |
AssignmentFixedMaterial | Boolean |
AssignmentFlag1-20 | Boolean |
AssignmentHasFixedRateUnits | Boolean |
AssignmentHyperlink | Text |
AssignmentHyperlinkAddress | Text |
AssignmentHyperlinkHref | Text |
AssignmentHyperlinkSubAddress | Text |
AssignmentLevelingDelay | Number |
AssignmentLinkedFields | Number |
AssignmentNotes | Text |
AssignmentNumber1-20 | Number |
AssignmentOverallocated | Boolean |
AssignmentOvertimeCost | Number |
AssignmentOvertimeWork | Number |
AssignmentPeakUnits | Number |
AssignmentPercentWorkComplete | Number |
AssignmentRegularWork | Number |
AssignmentRemainingCost | Number |
AssignmentRemainingOvertimeCost | Number |
AssignmentRemainingOvertimeWork | Number |
AssignmentRemainingWork | Number |
AssignmentResourceID | Number |
AssignmentResourceName | Text |
AssignmentResourceType | Number |
AssignmentResponsePending | Boolean |
AssignmentStart | Date |
AssignmentStart1-10 | Date |
AssignmentStartVariance | Number |
AssignmentSV | Number |
AssignmentTaskID | Number |
AssignmentTaskName | Text |
AssignmentTaskSummaryName | Text |
AssignmentTeamStatusPending | Boolean |
AssignmentText1-30 | Text |
AssignmentUniqueID | Number |
AssignmentUnits | Number |
AssignmentUpdateNeeded | Boolean |
AssignmentVAC | Number |
AssignmentWork | Number |
AssignmentWorkContour | Number |
AssignmentWorkVariance | Number |
This table is normally used in conjunction with the Tasks table to display detailed information on the successors of a task.
Column Name | Data Type |
---|---|
Project | Text |
TaskUniqueID | Number |
SuccessorLag | Number |
SuccessorPath | Text |
SuccessorTaskUniqueID | Number |
SuccessorType | Number |
This table is normally used in conjunction with the Tasks table to display detailed information on the predecessors of a task.
Column Name | Data Type |
---|---|
Project | Text |
TaskUniqueID | Number |
PredecessorLag | Number |
PredecessorPath | Text |
PredecessorTaskUniqueID | Number |
PredecessorType | Number |
Column Name | Data Type |
---|---|
Project | Text |
TaskUniqueID | Number |
SplitFinish | Date |
SplitStart | Date |
Column Name | Data Type |
---|---|
Project | Text |
TaskUniqueID | Number |
BaselineField | Number |
BaselineSplitFinish | Date |
BaselineSplitStart | Date |
Column Name | Data Type |
---|---|
CalendarUniqueID | Number |
Project | Text |
ResourceUniqueID | Number |
CalendarBaseCalendarUniqueID | Number |
CalendarIsBaseCalendar | Boolean |
CalendarName | Text |
This table is used to get a list of lookup table values for custom outline code fields.
Column Name | Data Type |
---|---|
OutlineCode | Number |
Project | Text |
OutlineCodeLookupDescription | Text |
OutlineCodeLookupIndex | Number |
OutlineCodeLookupLevel | Number |
OutlineCodeLookupParent | Number |
OutlineCodeLookupValue | Text |