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:
Start Microsoft Access 2000 and create a new database by clicking Access database wizards, pages, and projects.
Click the General tab of the New dialog box, and then double-click Data Access Page.
Click Design View on the New Data Access Page dialog box.
Click the Provider tab of the Data Link Properties dialog box, and then click Microsoft Project 9.0 OLE DB Provider.
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.)
Enter the path and file name of the project you want to access.
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=" & 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
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=" & 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
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 |