Microsoft Project 2000 OLE DB Provider Information

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Microsoft Corporation

October 2001

Applies to:

Microsoft Project 2001 SR1

Summary This article is intended to provide the information necessary to access Microsoft Project data through the Microsoft Project 9.0 OLE DB Provider.

On This Page

Overview
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)
Tables

Overview

This article provides you with information to access Microsoft® Project® data through the Microsoft Project 9.0 OLE DB Provider. This article describes the OLE DB table structures, some of the limitations and idiosyncrasies of the provider, and how to access the table structure using data access pages and sample ActiveX Data Objects (ADO) code.

Specifics

There are a few aspects of the OLE DB provider for Microsoft Project that are unique and should be noted to prevent unexpected results:

  • There are four data types: Text, Number, Boolean, and Date (as it appears in the interface, for example, 12/27/1999 10:30 AM).

  • Duration fields return minutes * 10 (for example, 8 hours is 4800) and Work fields return minutes * 1000 (for example, 8 hours is 480000).

  • Dates that would be "NA" in the interface return as 0.

  • A formula in a custom field that would result in a #ERROR in the interface will return the default value for the field.

  • Custom fields where no value has been set return the default value.

  • Custom field indicator fields where no indicator has been set return -1.

  • Work values for material resources are returned in the units defined in the interface, rather than minutes * 1000.

  • Using SELECT without WHERE returns null resource and task rows. Supplying WHERE will not return these same rows, even if they would otherwise qualify.

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.

Caution: 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.

Microsoft Project OLE DB Provider Limitations

The current implementation of the provider has a few limitations, as follows:

  • Read/write access is not supported.

  • Multi-table queries are not supported: You must use a separate query for each table you wish to access.

  • Access provided with forward-only cursors: Forward-only recordsets don't support methods such as MovePrevious, MoveFirst, or MoveLast. Also, forward-only recordsets don't support the use of the RecordCount property.

  • Joins are not supported. However, shaped recordsets can provide similar functionality by making relationships that had not previously existed between keys, fields, or rowsets. It is also possible to create hierarchical recordsets from a tabular format. For more information, see "Microsoft Data Shaping Service for OLE DB" in ActiveX Data Objects Help.

  • The ANY, LIKE, and IS NOT operators are not supported.

  • The aggregate functions Sum, Avg, Min, Max, Count, and StDev are not supported.

Accessing the OLE DB Table Structure Using Data Access Pages in Microsoft Access

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.

Sample code using ActiveX Data Objects (ADO)

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.

Accessing the provider on your computer

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=" &amp 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=" &amp FILE_NAME
    conData.ConnectionTimeout = 30
    conData.Open
    strSelect = "SELECT ResourceUniqueID, AssignmentResourceID, AssignmentResourceName, TaskUniqueID, AssignmentTaskID," &amp _
        &amp " 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 &amp "'" &amp rstAssigns.Fields(intCount).Name &amp "'" &amp _
                Space(30 - Len(rstAssigns.Fields(intCount).Name)) &amp vbTab &amp _
                    CStr(rstAssigns.Fields(intCount).Value) &amp vbCrLf
        Next
        strResults = strResults &amp 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

Accessing the provider on a Microsoft Project Central server

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 = "https://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=" &amp PC_SERVER &amp ";Remote Provider=Microsoft.Project.OLEDB.9.0;PROJECT NAME=" &amp 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 &amp rstTasks.Fields(intCount).Name &amp Space(30 - Len(rstTasks.Fields(intCount).Name))
    Next
    strResults = strResults &amp vbCrLf
    Do While Not rstTasks.EOF
        For intCount = 0 To rstTasks.Fields.Count - 1
            strResults = strResults &amp CStr(rstTasks.Fields(intCount).Value) &amp _
                Space(30 - Len(CStr(rstTasks.Fields(intCount).Value)))
        Next
        strResults = strResults &amp 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

Tables

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: An asterisk by a name indicates a column (field) common among more than one table.

Project

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 Tasks 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

Tasks

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

Resources

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

Assignments

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

Successors

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

Predecessors

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

TaskSplits

Column Name

Data Type

Project*

Text*

TaskUniqueID*

Number*

SplitFinish

Date

SplitStart

Date

BaselineTaskSplits

Column Name

Data Type

Project*

Text*

TaskUniqueID*

Number*

BaselineField

Number

BaselineSplitFinish

Date

BaselineSplitStart

Date

Calendars

Column Name

Data Type

CalendarUniqueID*

Number*

Project*

Text*

ResourceUniqueID*

Number*

CalendarBaseCalendarUniqueID

Number

CalendarIsBaseCalendar

Boolean

CalendarName

Text

CustomOutlineCodeLookupTables

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