Microsoft Project 2000 OLE DB Provider Information

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Microsoft Corporation

October 2001

Applies to:
   Microsoft Project 2000 SR1

Summary: This article provides the information necessary to access Microsoft Project data through the Microsoft Project 9.0 OLE DB Provider. (27 printed pages)

Contents

Overview
Specifics
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

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.

Top

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:

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.

Top

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 record sets 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.

Top

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.

Top

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.

Top

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

**Note   ** Lines of this code have been "broken" to fit within the page format.

Top

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 = "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

**Note   ** Lines of this code have been "broken" to fit within the page format.

Top

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.

Top

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.

Top

Tasks

Top

Resources

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

Top

Assignments

Top

Successors

This table is normally used in conjunction with the Tasks table to display detailed information on the successors of a task.

Top

Predecessors

This table is normally used in conjunction with the Tasks table to display detailed information on the predecessors of a task.

Top

TaskSplits

Top

BaselineTaskSplits

Top

Calendars

Top

CustomOutlineCodeLookupTables

This table is used to get a list of lookup table values for custom outline code fields.

Top