Microsoft KB Archive/143481: Difference between revisions
m (Text replacement - "<" to "<") |
m (X010 moved page Microsoft KB Archive/Q143481 to Microsoft KB Archive/143481 without leaving a redirect: Text replacement - "Microsoft KB Archive/Q" to "Microsoft KB Archive/") |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1,184: | Line 1,184: | ||
With rs | With rs | ||
Do While Not .EOF | Do While Not .EOF | ||
If !Reserved_hasnotes < | If !Reserved_hasnotes <> 0 Then | ||
' Extract string | ' Extract string | ||
strData = StrConv(rs!Reserved_BinaryProperties, vbUnicode) | strData = StrConv(rs!Reserved_BinaryProperties, vbUnicode) | ||
| Line 1,218: | Line 1,218: | ||
’Open the Project_Information table to look for Pool projects Set rs = CurrentDb.OpenRecordset(“Project_Information”, dbOpenTable) | ’Open the Project_Information table to look for Pool projects Set rs = CurrentDb.OpenRecordset(“Project_Information”, dbOpenTable) | ||
‘Enumerate across the recordset looking for notes With rs Do While Not .EOF If !ResourcePool < | ‘Enumerate across the recordset looking for notes With rs Do While Not .EOF If !ResourcePool <> 0 Then ’Extract string strData = StrConv(rs!Reserved_BinaryProperties, vbUnicode)’ This is the trick you have to do to extract ANSI…. intSize = GetIntegerFromPosition(strData, 1) ’ Size starts at position 1 intOffset = GetIntegerFromPosition(strData, 5) ’ Offset starts at 5 strExtracted = Mid(strData, intOffset + 1, intSize) Debug.Print strExtracted | ||
<pre> End If | <pre> End If | ||
Latest revision as of 19:16, 12 August 2020
PRJ98: Contents of the Database.wri File (Part 1 of 2)
PSS ID Number: Q143481 Article last modified on 09-30-1998
WINDOWS:98
WINDOWS
======================================================================
The information in this article applies to:
- == Microsoft Project 98 for Windows ==
SUMMARY
The More Information section of this article contains the first half of the contents of the Database.wri file. To obtain part two, please see the following article in the Microsoft knowledge Base:
ARTICLE-ID: Q143482 TITLE : PRJ98: Contents of the Database.wri File (Part 2 of 2)
NOTE: The Database.wri file is located in the Office folder on the Microsoft Project 98 compact disc.
MORE INFORMATION
Microsoft Project Database Format
August 1997
(C) 1997 Microsoft Corporation. All rights reserved.
Contents
Working with Projects in a Database - Supported Databases - Loading Microsoft Project 4.0 and 95 Projects That Were Stored in a Database - Database Permissions and Configuration - Ensuring Data Integrity in a Project in a Database - Precedence for Field Calculations When Importing Data - Cross-Language Usage and the Text Conversion Tables Adding and Changing Records in the Database - Working with Microsoft Project Data in the Database - Microsoft Project Tables - Specifying Times with Dates - Duration Values, Work Values, and Rate Values - Creating the Microsoft Project Database (MPD) Structure - Deleting a Project from a Database - DSN Requirements for Multiple Users and Projects - Concurrent Usage and Project Locking - Creating a New Task - Creating a New Resource - Creating a New Assignment - Creating Task Dependencies - Creating a New Project - Specifying the Value of a Custom Field - Specifying Custom Text Fields - Specifying Custom Number Fields - Specifying Custom Date Fields - Specifying Custom Duration Fields - Specifying Other Task and Resource Text Values - Creating an Inserted Project - Creating (or Modifying) an Assignment Remaining Work Contour - Creating Splits in Scheduled Work - Creating (or Modifying) an Assignment Actual Work Contour - Creating (or Modifying) Cost Contours - Creating (or Modifying) Task Percent Complete Contours - Setting the Contour Table Flags - Creating a New Calendar - Creating Calendar Exceptions - Modifying Resource Rates - Creating a Recurring Task - Using the Text Conversion Tables - Accessing and/or Modifying Other Data in the Database - Entering Total Actual Work on an Assignment or Task - Outlining with Summary Tasks and Subtasks - Editing Work on a Summary Task Assignment - Reading and Writing Notes Fields in the Database - Getting the Names of Sharer Files - Retrieving Workgroup Message Status
Working with Projects in a Database
Supported Databases:
Microsoft Project supports the following databases through ODBC:
- Microsoft Access 8.0
- Oracle Server, version 7.3 server and client
- Microsoft SQL Server 6.5 with Service Pack 3 or higher.
Microsoft Project can also make the ODBC connection automatically when writing to and reading from Microsoft Access 8.0 databases if you directly select Microsoft Project Database or Access 97 Database as the file type in the File Open and File Save dialog boxes.
NOTE: Saving or loading data is not supported with tables that are linked in Microsoft Access such that the data exists in another application and Microsoft Access is just providing the connection. To access the data you must actually import it into Microsoft Access or connect to the source directly.
Loading Microsoft Project 4.0 and 95 Projects That Were Stored in a Database:
You can open a Microsoft Project 4.0 and 95 project saved to database in Microsoft Project 98, but if you save the project to a database again, it will be saved with the Microsoft Project 98 database structure.
Database Permissions and Configuration:
Microsoft Project has three levels of operations it performs on a database, each requiring a corresponding set of permissions.
To open a project read-only, and view projects in a database, the user must have SELECT permission.
To modify existing projects in a database, or to save a new project to pre- existing tables in a database, or to delete a project from a database, the user must have the following permissions: SELECT, INSERT, UPDATE, DELETE.
To save a new project to an empty database, or to selectively import and export data, the user must have the following permissions: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE.
For all three permission levels above, the user must have the specified permissions on all tables created or accessed by Microsoft Project, and at no time should different column-level permissions be used.
If using Oracle Server, Microsoft Project does not support the user ID “internal” for saving and opening projects. When granting permissions to a specified user ID on Oracle, that user ID must have views created pointing to the original tables in order to see the projects. With full privileges, the user ID will be able to open, modify, or create projects through those views.
If using Microsoft SQL Server and the tables are created by the system administrator (SA), permissions can be granted to a specific user ID and no views are required to see the tables. With full privileges, the user ID will be able to open, modify, or create projects. If permissions are granted to allow access to the tables of a specific SQL Server user ID, then views pointing to the original tables must be created in order for that user ID to see the projects. In this case, the projects can only be opened for viewing, as you cannot save or modify projects in the database if you are using views with SQL Server to access any of the Microsoft Project tables.
For databases that support granting privileges on cursors, Microsoft SQL Server requires that the user have execute privileges on keyset driven cursors, while Oracle and other databases require the user to have execute privileges on static cursors.
Oracle users must set the open_cursors, shared_sql_area, and db_block_buffers in the “INITxxx.ORA” file (xxx is the Oracle SID), depending on how many projects need to be accessed at one time. Each open project session by any user on the server requires the following:
- 225K of shared_sql_area
- At least 10 db_block_buffers
For the total number of projects that any single user will open at one time requires the following:
200 open_cursors per project
Examples:
For a single user to have simultaneous access to a master project and 19 inserted projects requires the following minimum settings:
open_cursors=4000 (i.e., 20 maximum open projects by any user * 200)
shared_sql_area=4500 (i.e., 20 projects * 225K)
db_block_buffers=200 (i.e., 20 projects * 10)
For 5 users to simultaneously open 5 projects each on a server where no user will ever open more than 10 projects at once requires the following minimum settings:
open_cursors=2000 (i.e., 10 maximum open projects by any user * 200)
shared_sql_area=5625 (i.e., 5 users * 5 projects each * 225K)
db_block_buffers=250 (i.e., 5 users * 5 projects each * 10)
Ensuring Data Integrity in a Project in a Database:
Because Microsoft Project writes to and reads from a certain database structure, some changes to a database may cause corruption to a project in a database and prevent Microsoft Project from opening or saving part, or all of the project. The following actions could corrupt a project stored in a database:
- Changing the values of reserved fields and tables.
- Renaming a column or table.
- Deleting a column or table.
- Changing the data type of a column or table to an incompatible data type.
Microsoft Project will perform some data verification when you read a project from a database (or any other external format). The following cases may cause Microsoft Project to display an alert, change data to an appropriate value, or not read the data at all:
- The data type is incompatible.
- The value is out of range.
- The value would create inconsistencies in a project.
- The value is part of an interdependent mathematical relationship.
- The value is a not editable by the user and always calculated by Microsoft Project.
The data in some Microsoft Project fields is stored in binary format in the Reserved_InternalCompareBits database field. If you make an external change to a value in a Microsoft Project field, while the project is stored in a database, the binary field will contain the original value that was stored when you last saved the project to a database from Microsoft Project.
Microsoft Project will use the information in the Reserved_ExternalChangeData field in the Assignment_Information table, Task_Information table, Resource_Information table, and Task_Dependencies table to help determine which fields have been modified in the database, and these modifications will be preserved if they do not cause one of the above listed conditions. Information in the following fields will also be stored in binary format in the Reserved_ExternalChangeData field:
Task Fields ———–
% Complete % Work Complete Actual Cost Actual Duration Actual Finish Actual Start Actual Work Constraint Date Constraint Type Cost Duration Finish Fixed Cost Fixed Cost Accrual ID Leveling Delay Remaining Duration Remaining Work Start Stop Work
Resource Fields —————
Accrue At Available From Available To ID
Assignment Fields —————–
Actual Cost Actual Finish Actual Overtime Work Actual Start Actual Work Assignment Delay Assignment Units Finish Leveling Delay Overtime Work Regular Work Remaining Overtime Work Remaining Work Start Work
Constraint Fields (in the Task_Dependencies table) ————————————————–
LinkLag LinkType
Precedence for Field Calculations When Importing Data:
When you import data from any source, Microsoft Project will recalculate the fields in the project data based upon an order of precedence. If there are inconsistent values in any set of two interdependent fields, Microsoft Project maintains the value in the field with the highest precedence, and recalculates the values of the other field. If there are three interdependent fields with inconsistent values, Microsoft Project will maintain the values of the two fields with the highest precedence, and recalculate the value of the third.
When opening a full project from a database, Microsoft Project will first verify if any external changes to the data can be preserved by looking at the binary data in the Reserved_ExternalChangeData field. If the data in this field indicates that more than one interdependent value has changed and the new values are not consistent, then the order of the precedence listed below will be used to resolve the inconsistencies.
If the data in an Assignment field and the corresponding Task field is inconsistent, the data in the Assignment field will take precedence.
The order of precedence listed below always applies when you open an entire project from a database with Microsoft Project. If you open only part of a project from a database, there may be other factors that determine which fields take precedence in resolving inconsistencies between interdependent fields.
The order of precedence follows:
Assignment Fields —————–
Actual Start Actual Finish Start Finish Overtime Work Work Actual Work Remaining Work Assignment Units Assignment Delay Leveling Delay
The order of precedence for the Finish and Start fields is reversed for projects scheduled from a finish date.
Task Fields ———–
Actual Start Actual Finish Leveling Delay Start Finish % Complete Constraint Date Constraint Type Work Actual Work Remaining Work Actual Cost Fixed Cost Cost Actual Duration Duration Remaining Duration % Work Complete Stop
The order of precedence for the Finish and Start fields is reversed for projects scheduled from a finish date.
Cross-Language Usage and the Text Conversion Tables:
To enable different language versions of Microsoft Project to read a project in a database, the contents of some fields will be converted to numeric constants. Microsoft Project writes two tables to the database containing the conversion information for those fields, Intl_FieldReferences, which contains the mapping between the enumerated field categories and the field name, and Intl_TextConversions, which contains the mapping between the numeric constants and the possible text values for each field. The following field categories are converted to numeric constants:
Field Category Fields in this Category From Table ————————————————————-
Weekday Weekday Calendar_Working_Times
Schedule Start ScheduleFrom Project_Information
Accrual AccrueAt Resource_Information FixedCostAccrual Task_Information DefaultFixedCostAccrual Project_Information
Link Type LinkType Task_Dependencies
Display Units LinkLagDisplayUnits Task_Dependencies DelayDisplayUnits Assignment_Information DurationDisplayUnits Task_Information BaselineDurationDisplayUnits Task_Information DelayDisplayUnits Task_Information DurationDisplayUnits Custom_Duration_Fields
Cost Rate Units StandardRateDisplayUnits Resource_Information OvertimeRateDisplayUnits Resource_Information StandardRateDisplayUnits Resource_Rates OvertimeRateDisplayUnits Resource_Rates
Work Contour Type WorkContour Assignment_Information
Constraint Type ConstraintType Task_Information
Priority Priority Task_Information
Task Type Type Task_Information DefaultTaskType Project_Information
Calendar Working Working Calendar_Working_Times Working Calendar_Exceptions
Container Type ContainerType Custom_Duration_Fields ContainerType Custom_Date_Fields ContainerType Custom_Number_Fields ContainerType Text_Fields
Field ID FieldID Text_Fields FieldID Custom_Number_Fields FieldID Custom_Date_Fields FieldID Custom_Duration_Fields
Workgroup Messages Workgroup Resource_Information
Currency Symbol CurrencyPosition Project_Information Position
The Intl_FieldReferences and Intl_TextConversions tables are described in detail below, under Microsoft Project Database Structure.
You can store projects from different language versions of Microsoft Project into the same database, although only the text conversion strings for the first language version saved will be stored in the Intl_TextConversions table.
In order to read a project from a database saved with a different language version of Microsoft Project you need to have the correct NLS code page installed. The NLS code page value that Microsoft Project stores in Reserved_NLSCodePage, in the Project_Information table is the NLS (Code) page that the language requires. Here are the code page values for each set of languages:
Windows code page Description ———————————
1250 Windows Latin 2 (Central Europe) 1251 Windows Cyrillic (Slavic) 1252 Windows Latin 1 (ANSI) 1253 Windows Greek 1254 Windows Latin 5 (Turkish) 1257 Windows Latin 4 or Baltic 932 Japanese 949 Korean 936 Simplified Chinese 950 Traditional Chinese 1200 Unicode (UTF-8)
If you selectively save partial project data to a database, Microsoft Project will create the Intl_FieldReferences and Intl_TextConversions tables in the database, if they do not already exist in the database. It will not create them if they do exist.
Microsoft Project uses English for the names of the fields and tables in the database, in each language version. If you change the name of a table in a database you will likely corrupt the database, and Microsoft Project will not be able to read that table, and perhaps the entire project.
Adding and Changing Records in the Database
Working with Microsoft Project Data in the Database:
When working with Microsoft Project data in a database, you should always follow the guidelines described in the sections below.
Microsoft Project Tables:
You should never delete any tables created by Microsoft Project, and you should never delete any of the table columns. You also should not change the data type of any database field. A number of tables and fields created by Microsoft Project have names that begin with the word “Reserved.” You should never alter these tables or fields in any way.
You may notice that some of the tables Microsoft Project created in the database contain some records with large negative values in the UniqueID field. These records usually appear at the top of a table or at the beginning of each project and the values are -65536, - 65535, and -65534. These records are used internally by Microsoft Project and should never be edited or deleted.
If you create the tables yourself (see the section “Creating the Microsoft Project Database (MPD) Structure”), you should never make any of the fields a required field.
Specifying Times with Dates:
When you enter a date value directly in the database, you should always include the time with the date. Since database date and time fields use a default time when you don’t specify the time, relying on the default can lead to unexpected results. The database fields usually default to 12:00 AM, which is normally non- working time in most calendars. When Microsoft Project encounters 12:00 AM, it “rounds” the time to the next closest working time for start times and to the last working time for finish times.
Thus, if you specify dates without times, it can result in the following situation: You create what you think is a 5-day task in the database by specifying the task start as Monday’s date and the task finish as Friday’s date. The Monday time in the database is really Monday at 12:00 AM, so Microsoft Project treats this as Monday 8:00 AM (per the calendar default start time), which works fine. For Friday’s time, though, the default is Friday at 12:00 AM, which are rolled back to Thursday at 5:00 PM (the calendar default finish time). Thus, your 5-day task becomes only 4 days when it is read in by Microsoft Project. By explicitly specifying the time in each date/time field, you can always avoid this problem.
Duration Values, Work Values, and Rate Values:
Microsoft Project saves all duration and rate fields to two separate fields in the database. The first field is the duration value, work value, or rate value, and the second field is the corresponding DisplayUnits field. If you change the value in a DisplayUnits field in the database, it will have no effect on the value of the corresponding duration, work, rate or cost field, which Microsoft Project saves as absolute values. The DisplayUnits field simply indicates which unit label Microsoft Project will use to display the value.
Because duration, work, rate and cost values can be displayed using different units, Microsoft Project saves each using a standard multiple:
- Duration values are saved as minutes * 10. Eight hours would be saved as 4800 (i.e., 86010).
- Work values are saved as minutes * 1000. Eight hours would be saved as 480000 (i.e., 8601000).
- Rate values are saved as dollars per hour. Fifteen dollars an hour would be saved as 15.
- Cost fields are saved as dollars * 100. Seventy dollars and twenty-five cents would be saved as 7025.
Creating the Microsoft Project Database Structure:
If you have a need to create a Microsoft Project database from scratch, the easiest method is to save out an empty project using Microsoft Project and then delete the project from the database with the DeleteFromDatabase method, which will retain the table structure. If you do not want to use Microsoft Project to facilitate the process, you will need to create an empty database through the ODBC driver and then create all of the appropriate tables. Creating all of the tables manually would be an extensive undertaking, as you would need to use this document as a reference and ensure that you have exactly the right table and column names for every table and the correct data types for every field. So, to facilitate table creation, Microsoft Project includes three script files with the SQL statements necessary to set up all of the tables for Microsoft Access, Microsoft SQL Server, or Oracle Server. These script files are called MPDtable.sql, SQLtable.sql, and OraTable.sql, and can be found on the Microsoft Project 98 installation CD in the Database subdirectory under the ValuPack.
Before using the script for Oracle or SQL Server, you must open the file in a text editor and replace all occurrences of the string “<owner>” (without the quotation marks) with the name of the owner for the Microsoft Project tables. Also note that in all three scripts, the SQL statements that populate the Intl_FieldReferences and Intl_TextConversion tables are for the English conversion values and, therefore, will need to be modified for other languages.
Deleting a Project from a Database:
To delete a project from a database, you can use the DeleteFromDatabase method. For more information about this method, and other Microsoft Visual Basic for Applications objects, methods and properties, look for the appropriate topic in Microsoft Project 98 VBA Help.
DSN Requirements for Multiple Users and Projects:
If multiple users will be accessing project files in the same database, each user making changes to the data must use the same Data Source name (DSN) for connecting to the database. Microsoft Project combines the DSN and project name (in “<DSN>” format) as the identifier to locate a project. If someone uses a DSN with a different name, it will result in unresolved references for items such as inserted projects, cross- project links, and sharer projects utilizing the same resource pool.
This also applies to projects that are stored in a Microsoft Access database (as an MPD or MDB file). If multiple users will be modifying the database, then those users should all access the database with a same-named DSN and not do any saves by selecting the MPD or MDB file-type from the “Save as type” list in the File Save dialog. Because of the way Microsoft Project caches login passwords and other connection information, the same DSN cannot be used by more than one user ID on a single machine for different simultaneous logins to the database. Once a DSN connection is established, that connection will be reused even if a different login name and/or password is entered at login time. To login through a different user ID, you must first close all projects opened with the DSN or, for simultaneous connections, you must create additional alternate DSNs to use with each different user ID.
ODBC now supports the use of File DSNs in addition to traditional Machine DSNs, but Microsoft Project does not support the use of File DSNs in any situation where another project is referenced. Whenever Microsoft Project must refer to another project, certain information must be stored to be able to later locate that project. File DSNs do not provide the amount of information that is needed to be able to retrieve a project. Therefore, you should not use File DSNs when using features where other projects are referenced (e.g., project consolidation, cross-project linking, resource sharing links, and OLE links).
Concurrent Usage and Project Locking:
If you open a project in a database through Microsoft Project, and that project is not in use by another user, you will be given full read/write access. Until you finish your session with the project, you or any other user will only be able to open that project from that database as read- only.
The read/write access permission and some other properties used in managing concurrent usage are all maintained in a number of fields in the Project_Information table in the database. These project- locking fields are in effect only when users are using Microsoft Project to read or update the database. Microsoft Project does not provide any kind of locking when a database is being read or updated directly by a user using a database program or tool. Thus, any program or tool written to read or update the database should follow the same conventions to ensure consistent data access. Each of the project- locking fields is described below.
If you have the project open with read/write access through Microsoft Project, then Microsoft Project will store a value of “1” in the Project_ReadWrite field. When you finish your session and close the database, the field value will be set to “0” which is the default. You should temporarily set the value of this field to “1” to prevent other users from updating the project through Microsoft Project if you want to make updates to the database directly.
While any users are currently in the process of opening a project read-only from a database through Microsoft Project, Microsoft Project will store in the Reserved_ReadCount field the number of users actively reading in data at that moment.
While Microsoft Project is writing to a database, it will set the value of the Project_Locked field to “1”. While this field has a value of “1”, the project may not be opened by any user, not even read-only. You can temporarily set the value of this field to “1” to prevent other users from opening a project in the database, but you should not set it unless the Reserved_ReadCount field is at “0” (and you should not modify Reserved_ReadCount yourself). Before you set the Project_Locked field, the Project_ReadWrite field should be set to “1”.
Microsoft Project stores a string in the UserMachineID field in the database, which identifies the machine that has the project open with read/write access through Microsoft Project at any one time. If you access the project through a data source name, then Microsoft Project will store a string in the Reserved_DataSourceName field, identifying the data source name.
It’s recommended that, after setting Project_ReadWrite to “1”, you enter an appropriate string in the UserMachineID field so that any user attempting to open the project through Microsoft Project will get an appropriate message informing them that the project is currently opened for read/write by the correct name, otherwise Microsoft Project can’t identify to the user who has the project open for read/write access. The name should be an identifier for the user opening the project, or the program opening the project. When you are ready to allow read/write access to the project again, you should set the UserMachineID field back to a null string right before you reset the Project_ReadWrite field to “0”.
Microsoft Project will store the time of the last update to a database in the Reserved_LastUpdateTimestamp field.
If a user has read/write access to a project in a database through Microsoft Project, and another user changes data in that project directly in the database, that change will not be reflected in Microsoft Project for the first user. In addition, if the first user saves project data back to the database, that data will overwrite any changes made directly in the database by the second user.
Creating a New Task:
To create a new task in the database, you must add a new record to the Task_Information table and enter values for at least the following fields:
Table Fields Notes ———————————————————————-
Task_Information ProjectID Must refer to a valid project in the Project_Information table.
TaskUniqueID Must be unique within the project.
TaskID Must be unique within the project.
Name The name of the new task.
Duration The duration of the new task.
Example:
Assume your current project has 22 tasks with TaskUniqueIDs 1 to 22 and TaskIDs 1 to 22. If you want to create a new 1-week task named “Research Competitors” and you want this task to be the eighth task in the project, you would add the following record to the Task_Information table:
ProjectID TaskUniqueID TaskID Name Duration ——————————————————————-
3 23 8 Research Competitors 24000
Obtain the correct ProjectID from the Project_Information table. For the TaskUniqueID, use 23 because it is the next available number. Set TaskID to 8, to make this task eighth in the list, but this also requires that the TaskIDs of each subsequent record be adjusted by one to make room (since TaskIDs must be unique). So, while not shown here, the original records with TaskIDs 8 to 22 must be renumbered with TaskIDs 9 to 23. Set the Duration to 24000, since one 8- hour day is specified as 4800 in the database and 5*4800=24000.
Creating a New Resource:
To create a new resource in the database, you must add a new record to the Resource_Information table and enter values for at least the following fields:
Table Fields Notes ————————————————————————–
Resource_Information ProjectID Must refer to a valid project in the Project_Information table.
ResourceUniqueID Must be unique within the
project.
ResourceID Must be unique within the
project.
Name The name of the new resource.
Creating a New Assignment:
To create a new assignment in the database, you must add a new record to the Assignment_Information table and make sure certain values are set for the associated task record in the Task_Information table. You must enter values for at least the following fields:
Table Fields Notes ————————————————————————–
Assignment_Information ProjectID Must refer to a valid project in the Project_Information table.
AssignmentUniqueID Must be unique within the
project.
TaskUniqueID Must refer to a valid record
for the same ProjectID in
the Task_Information table.
ResourceUniqueID Must refer to a valid record
for the same ProjectID in
the Resource_Information
table.
StartDate The assignment start date
and time.
FinishDate The assignment finish date
and time.
Task_Information StartDate The task start date and time.
ConstraintType The constraint type for the
task. The values can be
obtained from the
ConversionValue field in the
Intl_TextConversions table
where the FieldValue equals
20 (e.g., As Soon As
Possible = 0).
Creating Task Dependencies:
To create a new task link dependency in the database, you must add a new record to the Task_Dependencies table and enter values for at least the following fields (which will give you a simple Finish-to- Start link with zero lag):
Table Fields Notes ————————————————————————–
Task_Dependencies ProjectID Must refer to a valid project in the Project_Information table.
DependencyUniqueID Must be unique within the
project.
PredecessorTaskUniqueID Must refer to a valid
for the same ProjectID in
Task_Information table.
SuccessorTaskUniqueID Must refer to a valid
record for the same
ProjectID in the
Task_Information table.
If you want to specify lag when you create a link, then you must specify both of the following fields:
Table Fields Notes ————————————————————————–
Task_Dependencies LinkLag The amount of lag, specified as a duration value (i.e., minutes * 10).
LinkLagDisplayUnits The value representing the units to use when the LinkLag is displayed inside Microsoft Project. The range of values can be obtained from the ConversionValue field in the records in the Intl_TextConversions table where the FieldType is 9.
NOTE: It is not possible to create cross-project links in the database; you must create them inside Microsoft Project.
Creating a New Project:
To create an entirely new project in the database, you must add a new record to the Project_Information table and create a project summary task in the Task_Information table. You must enter values for at least the fields specified below. Then, to add the associated tasks, resources, and assignments to the project, you must create the task, resource, and assignment records as described above.
| Table Fields Notes |
|---|
| Project_Information ProjectID Must be unique within the table. |
ProjectName Must be unique within the table.
StartDate The project start date and time.
Task_Information ProjectID Specify the same ProjectID value as in the Project_Information table.
TaskUniqueID This value must be zero for a
project summary task.
TaskID This value must be zero for a
project summary task.
Task_Information [various] For the specific fields and Resource_Information their values, see the respective Assignment_Information sections on creating tasks, resources and assignments.
NOTES:
- If you only enter the minimal values specified above when creating a new project in the database, Microsoft Project will display a message upon opening the project that says the Standard calendar is missing. To prevent this warning from being displayed, you must also create a Standard calendar when creating a new project.
- For a new project created in the database, all of the Microsoft Project option settings will default to False, not to the normal Microsoft Project default values. To ensure Microsoft Project behaves in the desired way once the project is opened, all of the option values in the Project_Information table should be set to the desired settings.
Specifying the Value of a Custom Field:
Custom flag fields are stored in the database in the respective Task_Information, Resource_Information, and Assignment_Information tables and can be set directly in those tables. All other custom fields are stored in four special tables that are based on the custom field type. To specify the value of a custom field, you must add a new record to the appropriate custom field table and set a corresponding flag in the Project_Information table. To specify custom field values for a project summary task, the procedure is identical, but you must use the special TaskUniqueID of zero.
Specifying Custom Text Fields:
You must enter values in the following database fields to specify a custom text field:
Table Fields Notes ————————————————————————–
Text_Fields ProjectID This value must refer to a valid project in the Project_Information table.
ContainerType The ContainerType is 0 for task
fields, 1 for resource fields, or
3 for assignment fields.
UniqueID Depending on the ContainerType,
specify the corresponding
TaskUniqueID, ResourceUniqueID, or
AssignmentUniqueID value of the
record for which the custom field
is being set.
FieldID The field identifier of the custom
field, which can be obtained from
the ConversionValue field in the
Intl_TextConversions table.
TextValue The custom text value.
Project_Information Text_Field_Set Set this value to True.
Specifying Custom Number Fields:
You must enter values in the following database fields to specify a custom number field:
Table Fields Notes ————————————————————————–
Custom_Number_Fields ProjectID This value must refer to a valid project in the Project_Information table. ContainerType The ContainerType is 0 for task fields, 1 for resource fields, or 3 for assignment fields. UniqueID Depending on the ContainerType, specify the corresponding TaskUniqueID, ResourceUniqueID, or AssignmentUniqueID value of the record for which the custom field is being set.
FieldID The field identifier of the
custom field, which can be
obtained from the ConversionValue
field in the Intl_TextConversions
table.
NumberValue The custom number value.
Project_Information Custom_Number_ Field_Set Set this value to True.
Specifying Custom Date Fields:
You must enter values in the following database fields to specify a custom date field:
Table Fields Notes ————————————————————————–
Custom_Date_Fields ProjectID This value must refer to a valid project in the Project_Information table.
ContainerType The ContainerType is 0 for task
fields, 1 for resource fields, or 3
for assignment fields.
UniqueID Depending on the ContainerType,
specify the corresponding
TaskUniqueID, ResourceUniqueID, or
AssignmentUniqueID value of the
record for which the
custom field is being set.
FieldID The field identifier of the custom
field, which can be obtained from
the ConversionValue field in the
Intl_TextConversions table.
DateValue The custom date/time value.
Project_Information Custom_Date_ Field_Set Set this value to True.
Specifying Custom Duration Fields:
You must enter values in the following database fields to specify a custom duration field:
Table Fields Notes ————————————————————————–
Custom_Duration_Fields ProjectID This value must refer to a valid project in the Project_Information table.
ContainerType The ContainerType is 0 for
task fields, 1 for
resource fields, or 3 for
assignment fields.
UniqueID Depending on the
ContainerType, specify the
corresponding
TaskUniqueID,
ResourceUniqueID, or
AssignmentUniqueID value
of the record for which
the custom field is being
set.
FieldID The field identifier of
the custom field, which
can be obtained from the
ConversionValue field in
the Intl_TextConversions
table.
DurationValue The custom duration value.
DurationDisplayUnits The units to use when
displaying this duration
in Microsoft Project.
Project_Information Custom_Duration Field_Set Set this value to True.
Example:
Let’s say you want to enter the value 226 into the Number3 field for a particular resource that already exists in the database. Assume that the record for the resource in the Resource_Information table includes the following information:
ProjectID ResourceUniqueID Name ———————————–
42 17 Bob
To set the Number3 field for this resource to 226, add the following record to the Custom_Number_Fields table:
ProjectID ContainerType UniqueID FieldID NumberValue ————————————————————–
42 1 17 205521010 226
The ProjectID must match the resource record. The ContainerType is 1 in this case, because we are setting a resource custom field. The UniqueID is taken directly from the ResourceUniqueID field in the Resource_Information table. The FieldID value is taken from the ConversionValue field in the Intl_TextConversion table from the record where the ConversionText field equals “Resource Number3.” The NumberValue field contains the actual data.
In order for Microsoft Project to read this record, you must set the Custom_Number_Field_Set flag in the Project_Information table to True.
Specifying Other Task and Resource Text Values:
Three text data fields containing task information and three text data fields containing resource information are stored in the Text_Fields table rather than in the respective Task_Information and Resource_Information tables. One task field is SubprojectFile, which is covered in a separate section on creating inserted projects below. The other two task fields are WBS and Contact, and the three resource fields are Code, EmailAddress, and ResourceGroup. These five fields are handled the same way as the custom text fields. You must enter values in the following database fields to specify one of these task or resource fields:
Table Fields Notes ————————————————————————–
Text_Fields ProjectID This value must refer to a valid project in the Project_Information table.
ContainerType The ContainerType is always 0
(task) for the WBS and Contact
fields, or 1 (resource) for Code,
EmailAddress, or ResourceGroup.
UniqueID Specify the corresponding
TaskUniqueID (for WBS or Contact)
or ResourceUniqueID (for Code,
EmailAddress, or ResourceGroup)
of the record for which the value
is being set.
FieldID Specify the appropriate field
identifier (from the
ConversionValue field in the
Intl_TextConversion table):
WBS: 188743696
Contact: 188743792
Code: 205520906
EmailAddress: 205520931
ResourceGroup: 205520899
TextValue The string to store in the field.
Project_Information Text_Field_Set Set this value to True.
NOTE: The only WBS values that are stored internally in Microsoft Project (and, therefore, the only ones that will ever be written out) are WBS values that have been changed from the default values that are automatically assigned by Microsoft Project.
Example:
Let’s say you want to enter the values 3.2.4 and 3.2.5 into the WBS fields for two existing tasks in the database. Assume that the records for the tasks in the Task_Information table include the following information:
ProjectID TaskUniqueID ————————
3 24 3 25
To set the WBS fields for these tasks, the add the following records to the Text_Fields table:
ProjectID ContainerType UniqueID FieldID TextValue ————————————————————-
3 0 24 188743696 3.2.4 3 0 25 188743696 3.2.5
The ProjectID values must match the Task records. The ContainerType is zero in this case, because we are setting a task text field. The UniqueID values are taken directly from the TaskUniqueID field in the Task_Information table. The FieldID value is taken from the ConversionValue field in the Intl_TextConversion table from the record where the ConversionText field equals “Task WBS.” The TextValue field gets the actual WBS strings.
In order for Microsoft Project to read these records, you must set the Text_Field_Set flag in the Project_Information table to True.
Creating an Inserted Project:
The process for creating an inserted project in the database is a combination of the procedures for creating a new task and specifying the value of a custom text field. You must add new records to the Task_Information and Text_Fields tables with values for at least the following fields, as well as setting the flag in the Project_Information table:
| Table Fields Notes |
|---|
| Task_Information ProjectID This value must refer to a valid |
| project in the Project_Information |
| table. This is the ID of the master |
| project, not the inserted project. |
TaskUniqueID Must be unique within the master
project. This is the unique ID of
the inserted project task.
TaskID Must be unique within the master
project. This is the ID of the
inserted project task.
Text_Fields ProjectID Specify the same ProjectID value as in the Task_Information table.
ContainerType The ContainerType is always 0
(task) for inserted projects.
UniqueID Specify the same TaskUniqueID
value used for the inserted project
task in the Task_Information table.
FieldID The FieldID is always 188743706 for
inserted projects.
TextValue The full name of the inserted
project (i.e., path and filename or
database and project name).
Project_Information Text_Field_Set Set this value to True.
Example:
Let’s say you have two projects stored in a database and you want to make one of them an inserted project of the other. Assume the two projects are in an MPD file that you access with a data source (DSN) called “Projects” and the project names are “Master Project” and “The Subproject.” First you need to create a task in Master Project to hold the inserted project. This record in the Task_Information table would appear as follows:
ProjectID TaskID TaskUniqueID Name ————————————————-
1 5 5 My subproject
The TaskID and TaskUniqueID used here are just chosen for demonstration purposes and have no significance. The important issue to remember is that they must be unique among all the IDs for the current project. Once you have this task, you need to specify the name of the inserted project file, and this is done the same as the custom text fields described earlier. This is the record that you need to add to the Text_Fields table:
ProjectID ContainerType UniqueID FieldID TextValue ———————————————————
1 0 5 188743706 <Projects>Subproject
The ProjectID value must match the Task record. The ContainerType is zero in this case, because we are setting a task text field. The UniqueID value is taken directly from the TaskUniqueID field in the Task_Information table. The FieldID value is taken from the ConversionValue field in the Intl_TextConversion table from the record where the ConversionText field equals “Task SubprojectFile.” The TextValue field gets the actual project name, which, in this case, is specified in the <DSN>syntax for a project in a database.
In order for Microsoft Project to read this record, you must set the Text_Field_Set flag in the Project_Information table to True.
Creating (or Modifying) an Assignment Remaining Work Contour:
To create or modify an assignment remaining work contour, you must add one or more records to the Assignment_Remaining_Work table. Each record represents a segment of the contour with a consistent unit value. When the units change, a new segment is required. The order of the work contour segments is dependent on the actual order of the UniqueIDs. If you add or insert a new segment, it may require renumbering of the existing values in the UniqueID field. Anytime you add or modify a record in a contour table, you must set a bit in the Flags field to indicate that a change has been made.
When the WorkValue and Units are both non-zero, the WorkingDuration field is not required. If either WorkValue or Units is zero, WorkingDuration must contain a value for how much working time the segment covers. Creating a split in an assignment is simply a matter of creating a record with a zero WorkValue and specifying the length of the split in the WorkingDuration field.
To create a new assignment remaining work contour segment, you must enter values for the following fields:
Table Fields Notes ————————————————————————–
Assignment_Remaining_Work ProjectID Must refer to a valid project in the Project_Information table. UniqueID A unique ID for this segment of the contour (must be unique within the project).All segments that make up a contour will be ordered by ascending UniqueIDs, so they must be numbered accordingly (sequential numbering is not required).
AssignmentUniqueID Must refer to a valid
record for the same
ProjectID in the
Assignment_Information
table.
FromDate This field is not read by
Microsoft Project, but is
a useful reference when
setting up a contour.
WorkValue The total work for this
segment of the contour.
Units The units for this
segment of the contour.
WorkingDuration The duration of this
segment if units or work
are zero (e.g.,
specifying the length
of a split).
Flags The second bit of this
value must be set (see
"Setting the Contour
Table Flags" below). If
creating a new record,
just set the value
of this field to 2.
NOTE: Contour records are applied on top of calendars to get the actual contour, thus the contour flows around non-working time. This is why there is only one record, for instance, for a task that occurs on Friday and Monday with the weekend off in-between. It is also the reason the FromDate field is for reference only - the real FromDate is calculated based on the calendar and all of the previous segments.
Example:
Assume a project contains the following assignment contours:
Sun Mon Tue Wed Thu Fri Sat Sun 2 Mon 2 Tue 2
Assignment 6 : 4h 4h 4h 4h Assignment 8 : 8h 8h 4h 4h
Microsoft Project saves these contours in the database in the Assignment_Remaining_Work table as follows (note that the Row # field is for reference in this example only and does not exist in the database):
Row Project Unique Assignment From Work Units Wrkng Flags # ID ID UniqueID Date Value Duration ————————————————————————–
6-1 3 17 6 Mon 8:00AM 960000 0.5 19200 0 8-1 3 19 8 Wed 8:00AM 960000 1 9600 524288 8-2 3 20 8 Thu 5:00PM 480000 0.5 9600 0
Let’s say you now want to modify the first contour to assign 8 hours of work on Tuesday, instead of 4, and in the second contour, you need to create a one- day split on Thursday. First, let’s look at the contour for Assignment 6. The modified Assignment_Remaining_Work table records appear as follows (all new and modified values are displayed in bold):
Row Project Unique Assignment From Work Units Wrkng Flags # ID ID UniqueID Date Value Duration ————————————————————————–
6-1 3 17 6 Mon 8:00AM 240000 0.5 2 6-2 3 18 6 Tue 8:00AM 480000 1 2 (new) 6-3 3 21 6 Wed 8:00AM 480000 0.5 2 (new)
Since the Units value needs to change to 100% on Tuesday, it means a new record (6-2) needs to be added. Furthermore, the insertion of that new record means you need to change the Units back to the previous level on Wednesday. So, you must add another record (6-3), for a total of three records to cover all of Assignment 6. Remember, the FromDate is strictly for reference and will not be read by Microsoft Project.
The WorkValue in 6-1 is reduced to reflect just 4 hours of work and the Flags field bit is set, making that value 2. In 6-2, the UniqueID 18 is used, since it’s the next available number. The WorkValue and Units are set to reflect 8 hours on one day and the Flags field bit is set. In 6-3, the Unique ID is set to 21, since 19 and 20 are already in use (by Assignment 8). The WorkValue is set to 8 hours and the Units is set to 50% to make the work occur over two days. Lastly, the 6-3 Flags field bit is set.
For Assignment 8, to add a one-day split on Thursday, the modified Assignment_Remaining_Work table records appear as follows (again, the additions and changes are marked in bold):
Row Project Unique Assignment From Work Units Wrkng Flags # ID ID UniqueID Date Value Duration ————————————————————————–
8-1 3 19 8 Wed 8:00AM 480000 1 524290
8-1a 3 22 8 Thu 8:00AM 0 0 4800 2 (new)
8-1b 3 23 8 Fri 8:00AM 480000 1 2 (new)
8-2 3 24 8 Mon2 8:00AM 480000 0.5 2
To create the split, a new record (8-1a) with no work must be created for Thursday. This record for the split must specify WorkingDuration for the length of the split (8 hours in this case), since the WorkValue and Units cannot be used in determining the contour. The UniqueID used for 8-1a is 22, since 21 was used above when making Assignment 6 changes. The insertion of record 8-1a means that record 8-1 now represents only one day of work, so its WorkValue must be set to 8 hours. Record 8-1b must be newly created to contain the second day of work at 100% Units that was originally included in 8-1 before the split was inserted. The UniqueID for 8- 1b is set to 23, since that’s the next available number.
The WorkValue and Units in record 8-2 don’t need to change. Because of the split, the start date moves out to after the weekend, so FromDate is updated to make things clear, but it has no effect on the actual contour. Since the UniqueID fields must contain ascending values to specify the order of the segments in the contour, the UniqueID for 8-2 is no longer acceptable and must be changed from its original value of 20 to 24 (or higher). So, the only real change for 8-2 is the UniqueID, but that’s enough to require that the Flags bit be set.
In fact, all four Assignment 8 records are new or modified, so the Flags field bit must be set for each one. In 8-1, the Flags value was typical of the sort of large value that Microsoft Project may save out when a number of other bits representing internally used flags have already been set. Regardless of the original value, setting the bit just requires performing an OR with 2.
Creating Splits in Scheduled Work:
Creating a split in scheduled work is just a specific case of modifying the assignment remaining work contour such that one segment of the contour does not contain any work. This entails creating a record in the Assignment_Remaining_Work table with zero in WorkValue and the length of the split in WorkingDuration. This is covered in the “Creating (or Modifying) an Assignment Remaining Work Contour” section and example above.
Tasks without resources assigned go into the Assignment_Remaining_Work table as assignments for the Unassigned Resource. Thus, a split created in a task with no assignments will still appear in the Assignment_Remaining_Work table. So, if you are creating a project from scratch in the database and you need to add splits to unassigned tasks, the only difference in this case is that you must use the value -65535 for the ResourceUniqueID on the assignment records, since - 65535 is always the unique ID for the Unassigned Resource.
NOTE: In the case of “stop/resume” splits, where the split immediately follows actual work, the split information is stored in the Task_Information table in the Stop and Resume fields, not in the Assignment_Remaining_Work table. Stop/resume splits are often created by leveling, and they can also be created by dragging out the remaining portion of a task that is already marked with a partial percent complete.
Example:
Assume you have an existing 2-day task that has no resource assigned and you want to insert a 3-day split in the middle. The task is originally represented by a single record in the Task_Information table such as the following:
ProjectID TaskID TaskUniqueID Duration StartDate FinishDate ———————————————————————
6 7 5 9600 Mon 8:00AM Tue 5:00PM
To create the split, you need to create a contour in the Assignment_Remaining_Work table, which, in turn, relies on having an assignment record in the Assignment_Information table. Since there is no resource assigned to the task, you need to create the assignment under the Unassigned Resource. Because the task already exists in the database, the assignment already exists as well and you just need to reference it, but if you were actually creating everything from scratch, this is the record that would need to be added to the Assignment_Information table:
Project Assignment Task Resource Start Finish ID UniqueID UniqueID UniqueID Date Date ——————————————————————-
6 59 5 -65535 Mon 8:00AM Fri 5:00PM
The ProjectID is the same as the task record. The AssignmentUniqueID is just a value that is not already in use for this project. The TaskUniqueID value is taken directly from the task record in the Task_Information table. The ResourceUniqueID value is set to -65535 to indicate the Unassigned Resource. The StartDate is the same as the task record, but the FinishDate represents the new finish with the split incorporated (since the assignment takes precedence over the task, changing the FinishDate in the task record is not required).
To create the actual contour and indicate the length and position of the split, these are the records that you need to add to the Assignment_Remaining_Work table:
Project Unique Assignment From Work Units Working Flags ID ID UniqueID Date Value Duration ———————————————————————-
6 43 59 Mon 8:00AM 480000 1 2
6 44 59 Tue 8:00AM 0 0 14400 2
6 45 59 Fri 8:00AM 480000 1 2
In all three records, the ProjectID just matches the other tables and the AssignmentUniqueID is taken directly from the record just created in the Assignment_Information table. The sequence of the UniqueIDs determines the ordering of the contour segments, so the three UniqueIDs are chosen such that they are in ascending order and they don’t duplicate any others in the project. All three records also need to have the bit in the Flags field set so that they will be read by Microsoft Project.
The first record covers the 8 hours of work on Monday. The second record covers the split, hence the WorkValue and Units are set to zero and the WorkingDuration must contain the value to indicate 3 days. The third record covers the 8 hours of work on Friday.
Creating (or Modifying) an Assignment Actual Work Contour:
Creating or modifying an assignment actual work contour is identical to the procedure described above for the remaining work contours, but there is an additional requirement if any of the work occurred during non-working time (e.g., on the weekend). In that case you must add exception records for the non- working time.
When actual work is entered in the Assignment_Actual_Work table, Microsoft Project will decrease the work a complementary amount in the Assignment_Remaining_Work table contours upon reading the data (unless the bit in their Flags field is set, in which case Microsoft Project will retain whatever is in the remaining work contour as well).
NOTES:
- With exceptions in the Assignment_Actual_Exceptions table, the FromDate and ToDate fields do determine the actual range, so these values must both be entered. UniqueID ordering has no effect.
- It is important to make sure the Units values are correct for each segment of the contour when entering actual work in the database. If Microsoft Project has stored out some actuals, the Units value of the last segment of the contour may appear incorrect because there may have been additional remaining work on the last day. In that case, Microsoft Project sets the Units at a higher value so that any additional remaining work entered will start from the last day with actuals, rather than from the next day (i.e., the first day without actuals). If you add additional actual work contour segments in the database following a record with the inflated Units value, you should first set the Units of that segment to the correct value. By the same token, if you want to have some remaining work appear on the last day of the contour where you already have some actual work entered, you can increase the Units above the normal amount.
Example:
Assume your calendar covers the normal Monday-Friday 8:00 to 5:00 working time and you have a 2-day task that starts on Friday and ends on Monday. Assume also that there is only one resource assigned to this task and he decides to work 4 hours each day on Friday, Saturday, Sunday, and Monday, rather than the scheduled 8 hours each on Friday and Monday. If you want to create records to show the actual work exactly as it occurred, you not only need to create an actual work contour in the Assignment_Actual_Work table, you must also indicate the weekend hours in the Assignment_Actual_Exceptions table. These are the records that you must create in the Assignment_Actual_Work table:
Project Unique Assignment From Work ID ID UniqueID Date Value Units Flags ———————————————————————–
1 11 20 Fri 8:00AM 240000 0.5 2
1 12 20 Sat 12:00AM 480000 0.1667 2
1 13 20 Mon 8:00AM 240000 0.5 2
This is the exception record that you need to create in the Assignment_Actual_Exceptions table:
ProjectID UniqueID Assignment FromDate ToDate Flags UniqueID ———————————————————————-
1 11 20 Sat 12:00AM Mon 12:00AM 2
Since the work was evenly distributed on Saturday and Sunday, only one exception record is needed to cover the whole weekend. The FromDate and ToDate fields reflect this range.
In the actual work contour records above, the first and third records cover the 4 hours of actual work on Monday and Friday. The second record covers the 8 hours of actual work for both weekend days. The Units value is set to 16.67% to force 4 hours of actual work to be assigned to each day. The Units is determined by taking the actual work (8 hours) and dividing it by the total exception period (48 hours). Thus, 8 divided by 48 gives .1667 (fractional unit values should be entered to four decimal places to insure accuracy). All the records in both tables need to have the bit in the Flags field set so that they will be read by Microsoft Project.
If the actual work amounts were different on Saturday and Sunday and you wanted to represent them exactly as they occurred, separate actual work contour records would be required for each of the weekend days.
Creating (or Modifying) Cost Contours:
To create or modify an assignment actual cost contour, you must add one or more records to the Assignment_Actual_Cost table. In order for Microsoft Project to actually read the cost data, you must set a flag in the Project_Information table that indicates that costs are not to be calculated automatically by Microsoft Project.
Each record in the contour normally represents a segment of the contour with a consistent cost value. When the cost changes, a new segment is required. When creating contour segments itself, Microsoft Project creates records based on how the data was entered – a database record is created for each timephased value entered - so there may be multiple individual segments for a period even though the costs are uniform. For instance, entering $1000 at the weekly level will result in one record in the cost table, while entering $200 per day over the work week will result in five records in the cost table. In both cases, the cost values displayed inside Microsoft Project are the same, even though they are saved out to the database differently.
The order of cost contour segments is determined by the FromDate and ToDate fields, so segments do not need to be ordered by UniqueID values like work contours. Again, anytime a record in a contour table is added or modified, a bit must be set in the Flags field to indicate that a change has been made.
This information applies to all other cost contour tables as well (Assignment_Baseline_Cost, Resource_Baseline_Cost, Task_Baseline_Cost). Values are required in all of the following fields for each segment of the contour:
Table Fields Notes ————————————————————————
Assignment_Actual_Cost ProjectID Must refer to a valid project in the Project_Information table.
UniqueID A unique ID for this segment
of the contour (must be
unique within the project).
AssignmentUniqueID Must refer to a valid record
for the same ProjectID in
the Assignment_Information
table.
FromDate The start of this contour
segment.
ToDate The end of this contour
segment.
Cost The cost for this segment of
the contour.
Flags The second bit of this value
must be set (see "Setting
the Contour Table Flags"
below). If creating a new
record,just set the value of
this field to 2.
Project_Information AutoCalcActualCosts This value must be set to False for the data to be read.
NOTE: The AssignmentUniqueID field listed above will actually be a different field for some of the other cost contour tables. It will be ResourceUniqueID (from the Resource_Information table) for the Resource_Baseline_Cost table and it will be TaskUniqueID (from the Task_Information table) for the Task_Baseline_Cost table.
Example:
Assume you have a 3-day assignment that starts on Tuesday and you want to enter actual costs of $60.00 on Tuesday and $80.00 on both Wednesday and Thursday. These are the records that must be created in the Assignment_Actual_Cost table:
Project Unique Assignment From To Cost Flags ID ID UniqueID Date Date ————————————————————————
1 3 2 Tue 8:00AM Tue 5:00PM 6000 2
1 4 2 Wed 8:00AM Thu 5:00PM 16000 2
The FromDate and ToDate fields determine the exact range for each contour segment. Since the first day has a different cost value, it requires a separate segment. Wednesday and Thursday have the same cost value, so one segment can be created for both days with the total cost entered into the Cost field. For Microsoft Project to read these records, the bit must be set in both Flags fields and the AutoCalcActualCosts field in the Project_Information table must be set to False.
Creating (or Modifying) Task Percent Complete Contours:
To create or modify a task percent complete contour, you must add one or more records to the Task_Percent_Complete table. Working with percent complete is identical to working with cost contours, except that each record represents a segment of the contour with a consistent percent complete value, rather than cost. When the percent complete changes, a new segment is required. Values are required in all of the following fields for each segment of the contour:
Table Fields Notes ————————————————————————–
Task_Percent_Complete ProjectID Must refer to a valid project in the Project_Information table.
UniqueID A unique ID for this segment of
the contour (must be unique
within the project).
TaskUniqueID Must refer to a valid record
for the same ProjectID in the
Task_Information table.
FromDate The start of this contour
segment.
ToDate The end of this contour
segment.
PercentCompleted The percent complete for this
segment of the contour.
Flags The second bit of this value
must be set (see "Setting the
Contour Table Flags" below). If
creating a new record, just set
the value of this field to 2.
NOTE: Percent values are stored in the database as whole numbers from 0 to 100 (i.e., to enter 27% you would just enter the value 27).
Setting the Contour Table Flags:
Whenever a record in any of the contour tables in the database is modified, a bit in the Flags field must be set to let Microsoft Project know which records have changed. In most cases, setting the flag should be a simple matter, but the complexity depends on the method used to access the Flags field and whether it needs to be updated directly in the database.
The bit to be set is the second bit from the right (i.e., the twos bit in binary). The simplest case involves retrieving the existing value of the Flags field and performing a bitwise OR operation with the number 2, then substituting the result back into the Flags field. For example, setting a bit can be done in VBA as follows:
Flags = Flags OR 2
If you need to set the flag directly in the database, it can be done via the following SQL statement:
UPDATE
SET Flags = Flags + 2 WHERE <condition to identify record> AND ((Flags - ((Flags / 4) * 4)) <=1) If the database supports the MOD operator, the above SQL statement can be simplified to: UPDATE CalendarUniqueID Must be unique within the project.
IsBaseCalendar Set this value to True for new base
calendars.
BaseCalendarUniqueID Specifies the base calendar when
IsBaseCalendar is False. Must refer
to a valid record for the same
ProjectID in this table.
CalendarName The name of the new calendar.You are not required to specify working time for the new calendar because working time is determined by the base calendar, or is set to the default for new base calendars. If you want to specify working times, you can add records to the Calendar_Working_Times table with values for at least the following fields:
Table Fields Notes ————————————————————————–
Calendar_Working_Times ProjectID Must refer to a valid project in the Project_Information table.
UniqueID Must be unique within the
project.
CalendarUniqueID Must refer to a valid record
for the same ProjectID in the
Calendars table.
DayOfWeek Specify the appropriate
DayOfWeek value (from the
Intl_TextConversion table):
Sunday: 1 Thursday: 5
Monday: 2 Friday: 6
Tuesday: 3 Saturday: 7
Wednesday: 4
Working Set this flag to 0 if the
day is non-working or 1 if it
is working. When set to 1, at
least one start and end time
must be specified (in the
FromTime1/ToTime1 fields). For
resource calendars, you can
also use 2, which defaults to
the base calendar settings.
FromTime1 If Working is set to 1, the
starting time is required.
ToTime1 If Working is set to 1, the
end time is required.To specify specific time ranges for the working time, values can be entered for up to three ranges per day in the FromTime1/ToTime1, FromTime2/ToTime2, and FromTime3/ToTime3 field pairs. Because the database only supports a combined time and date format, you will need to enter the date along with the time, but Microsoft Project will ignore the date in these fields and only use the time portion.insertdoubleparaNOTE: Microsoft Project creates records in the Calendars and Calendar_Working_Times tables for the resource with UniqueID zero (these records usually have a CalendarUniqueID value of 2). These records should never be modified or deleted.
Example:
Assume you want to create a new base calendar for a part-time shift that works 9:00 AM to 1:00 PM Monday through Thursday and 8:00 AM to 12:00 PM on Friday. This is the record that needs to be added to the Calendars table:
ProjectID CalendarUniqueID IsBaseCalendar ————————————————–
2 4 1
The CalendarUniqueID is just a value that is not already in use for this project. Since we are creating a new base calendar, the IsBaseCalendar field is set to True and the BaseCalendarUniqueID does not need to be specified.
These are the records that need to be added to the Calendar_Working_Times table:
Project Unique Calendar DayOf Working From To ID ID UniqueID Week Time1 Time1 ————————————————————————-
2 15 4 1 0
2 16 4 2 1 <date> 9:00AM <date> 1:00PM
2 17 4 3 1 <date> 9:00AM <date> 1:00PM
2 18 4 4 1 <date> 9:00AM <date> 1:00PM
2 19 4 5 1 <date> 9:00AM <date> 1:00PM
2 20 4 6 1 <date> 8:00AM <date> 12:00PM
2 21 4 7 0
The UniqueID values are selected such that they are unique for the project. The CalendarUniqueID value is taken directly from the record just created in the Calendars table. The DayOfWeek values are 1 to 7, corresponding to Sunday through Saturday. Working is set to False on the Sunday and Saturday, and True the rest of the days. The <date> portion of the FromTime1 and ToTime1 fields is required by most databases, but it will be ignored by Microsoft Project, so any date can be entered. The time portion of these fields is set for each working day.
Creating Calendar Exceptions:
There are two kinds of calendar exceptions. One is where a particular day of the week always has the same exception, while the other is a one-time occurrence for a specific date and time. To create the first kind (e.g., Saturday morning is always working or Tuesday is always non-working), the exception needs to be created in the Calendar_Working_Times table. A record should be added with values for the fields described above with Workday set to the day of the exception and the FromTime and ToTime fields filled in as necessary.
To create a specific calendar exception in the database (e.g., December 26th is non-working), you must add a new record to the Calendar_Exceptions table and enter values for at least the following fields:
Table Fields Notes ————————————————————————–
Calendar_Exceptions ProjectID Must refer to a valid project in the Project_Information table.
UniqueID Must be unique within the
project.
(See the note below about the
ordering of UniqueID values).
CalendarUniqueID Must refer to a valid record for
the same ProjectID in the
Calendars table.
FromDate The starting date of the
exception.
ToDate The ending date of the
exception.
Working Set this flag to 0 if the
exception is non-working time, 1
if it is working time.As with the Calendar_Working_Times table, to specify a specific time range for an exception, values can be entered for up to three ranges per record in the FromTime1/ToTime1, FromTime2/ToTime2, and FromTime3/ToTime3 field pairs. Because the database only supports a combined time and date format, you will need to enter the date along with the time, but Microsoft Project will ignore the date in these fields and only use the time portion.
NOTES:
- The order of the calendar exception records is important and is determined by the UniqueID values. The ascending order of the UniqueIDs must sequence the exceptions in chronological order. If a new exception record is added or inserted, it may require renumbering of the existing values in the UniqueID field. Exceptions that are not placed in the proper chronological order by UniqueID will be ignored.
- If calendar exceptions conflict, one will be ignored, so exception records should not overlap. For example, if you wanted to make the month of July non- working, except for July 10th, you would need to create one non-working exception record for July 1-9 and one for July 11-31. You cannot create a non-working exception for July 1-31 and then another working exception for July 10th.
Modifying Resource Rates:
To set resource rates in the database, you must add one or more records to the Resource_Rates table and enter values for at least the following fields. The UniqueIDs and FromDates must be in ascending order in the database.
Table Fields Notes ————————————————————————–
Resource_Rates ProjectID Must refer to a valid project in the Project_Information table.
UniqueID Must be unique within the project and
in ascending order.
ResourceUniqueID Must refer to a valid record for the
same ProjectID in the
Resource_Information table.
RateTable Specify a value from 0 to 4,
representing the respective rate
table A to E in Microsoft Project.
FromDate The first date on which the rate is
effective, and must be in ascending
order.
StandardRate The standard rate for the period.Creating a Recurring Task:
While it is possible to create a recurring task in the database, it’s preferable to create recurring tasks inside Microsoft Project because the Recurring Task dialog in Microsoft Project will not reflect the actual recurring task settings for a recurring task created directly in the database. For recurring tasks created in the database, the Recurring Task dialog in Microsoft Project will always default to showing a weekly recurring task that occurs on Mondays with a 1d duration.
To create a recurring task in the database, you must add records to the Task_Information table for the summary recurring task and each of the reoccurring subtasks. The minimum set of values required is specified below. Most of the required values are the same for both kinds of recurring tasks, but summary recurring tasks require setting an additional flag, while recurring subtasks require constraint information.
Table Fields Notes ————————————————————————
Task_Information ProjectID Must refer to a valid project in the Project_Information table.
TaskUniqueID Must be unique within the
project.
TaskID Must be unique within the
project.
Name The name of the summary or sub
recurring task.
Duration This value is required for each
recurring subtask.
OutlineLevel The OutlineLevel of recurring
summary tasks should be one less
than that of the recurring
subtasks.
Recurring This flag should be set for both
recurring summary tasks and
subtasks.
RecurringTaskSummary This flag should only be set for
recurring summary tasks.
Rollup This flag should be set for both
recurring summary tasks and
subtasks.
StartDate The start of the summary task or
the subtask.
ConstraintType Required for recurring subtasks.
Usually set to 4 (SNET).
ConstraintDate Required for recurring subtasks.
HideBar This flag should be set for
recurring summary tasks to have
them display properly.Example:
Assume your project runs 4 weeks and you want to create a recurring task for a meeting that occurs every week on Wednesday from 3:00 to 4:00 PM. These are the records that need to be added to the Task_Information table to create the recurring task:
ProjectID TaskID Task Name Duration StartDate UniqueID ————————————————————————
4 14 14 Weekly Meeting 600 Wed1 3:00PM
4 15 15 Weekly Meeting 1 600 Wed1 3:00PM
4 16 16 Weekly Meeting 2 600 Wed2 3:00PM
4 17 17 Weekly Meeting 3 600 Wed3 3:00PM
4 18 18 Weekly Meeting 4 600 Wed4 3:00PM
(records continued.)
TaskID Outline Recurring Recurring Rollup Constraint Constraint Hide Level TaskSummary Type Date Bar —————————————————————————
14 1 1 1 1 1
15 2 1 1 4 Wed1 3:00PM
16 2 1 1 4 Wed2 3:00PM
17 2 1 1 4 Wed3 3:00PM
18 2 1 1 4 Wed4 3:00PM
The record with TaskID 14 is the summary recurring task. The OutlineLevel is 1 in this case, but could be something else, as long as the recurring subtasks are at one outline level higher. The Recurring, RecurringTaskSummary, Rollup, and HideBar flags are all set to True for the summary recurring task.
The remaining records represent the 4 weekly subtasks and all are at OutlineLevel 2 in this case, since the summary reccurring task was at level 1. Each has an SNET ConstraintType with an associated ConstraintDate to space the tasks out to occur in successive weeks. Only the Recurring and Rollup flags need to be set to True for the subtasks. Using the Text Conversion Tables:
If you want to display strings and values the same as they are displayed in Microsoft Project, you can extract the appropriate text strings from the text conversion tables. The Intl_FieldReferences table contains the categories of conversion text (e.g., duration units, weekday names, constraint types) and the Intl_TextConversions table contains the actual strings for each constant (e.g., Sunday, Monday, As Soon As Possible, Must Finish On).
The query required to extract the appropriate text is dependent upon the particular data in the database that you need to access. For example, the following query displays the task name, duration (as it is stored in the database) and the duration units as they would be displayed in Microsoft Project:
SELECT TASKS.Name, TASKS.Duration, TEXT.ConversionText FROM Task_Information TASKS, Intl_TextConversions TEXT WHERE TASKS.DurationDisplayUnits = TEXT.ConversionValue AND TEXT.FieldType = (SELECT FieldType FROM Intl_FieldReferences WHERE FieldName = ‘Display Units’)
For a task that has 2 day duration and appears as “2d” in Microsoft Project, the query will return “9600” and “d”. To actually show this as “2d”, you would need to divide the 9600 by 4800 (since 1 day is normally 4800 in the database) and then concatenate the label string. In reality, you would probably want to create a table with multiplication values based on the values of the ConversionText field in the Intl_TextConversions table and do multiplications on the duration. This will also need to be based off of the DefaultMinutesPerDay value stored in the Project_Information table.
Accessing and/or Modifying Other Data in the Database:
As opposed to having a minimal set of required field values that need to be specified in all of the cases above where new records are being created (or contours are being modified), making modifications to most other data in an existing project in the database usually consists of little more than entering a new value in place of the existing value. But there are still some specific scenarios where there are additional values required to be able to successfully modify database data. This section covers some of those cases as well as some other helpful information about extracting certain information from the database.
Entering Total Actual Work on an Assignment or Task:
If, instead of entering one or more assignment actual work contours, you want to enter a single value in the database for the total actual work for an assignment, you must enter data into several other task and assignment fields in addition to entering the actual work value into the ActualWork field in the Assignment_Information table. If the actual work entered will make the task 100% complete, you must also enter values for ActualStart and ActualFinish in the Task_Information table. If the amount of actual work being entered will not complete the task, you should enter values for ActualStart and StopDate in the Task_Information table and also make sure the ScheduledWork field in the Assignment_Information table contains an appropriate value.
Entering total actual work on a task (rather than on an assignment) will only work if there are no assignments on the task; otherwise, you must always enter the actual work on the assignment to have Microsoft Project read it in correctly. When entering actual work on a task with no assignments, you still need to enter values for the start and finish times of the actual work (i.e., ActualStart and either ActualFinish or StopDate) in the Task_Information table.
Outlining with Summary Tasks and Subtasks:
When creating summary tasks and subtasks in the Task_Information table in the database, the TaskID order comes into play. To create subtasks, the OutlineLevel should be set to one greater than the desired summary task, the summary task should have a lower TaskID value than all of the subtasks, and no other tasks at the same level of the summary task should have a TaskID that falls between the TaskIDs of the summary and the associated subtasks. In addition, the Summary flag should be set in the record for the desired summary task.
Editing Work on a Summary Task Assignment:
If you have a resource assigned to a summary task and you want to edit the work on that assignment in the Assignment_Information table in the database, you must specify values for both the ScheduledWork and Units fields in order for the edit to take effect.
Reading and Writing Notes Fields in the Database:
Microsoft Project writes the contents of the Rich Text Format notes fields to a non-editable binary field called Reserved_BinaryProperties, which also includes other binary information about the project. The first 255 characters of the notes field, or up to the first line end or first object, are also written to the Notes field corresponding to the type of note. If you make changes to that field they will be lost when you read the project back into Microsoft Project, since they will be over written by the notes information in the Reserved_BinaryProperties.
To extract the contents of Rich Text Format notes from a binary field, you can use the following Microsoft Access Basic code:
Option Compare Database Option Explicit
Sub getrtf() Dim db As Database Dim rs As Recordset Dim bytBuffer() As Byte Dim strData As String, strExtracted As String Dim intSize As Integer, intOffset As Integer
'This macro will look for Task Notes and extract the rtf.
'This rtf can then be written to file (that Word will understand),
'or displayed in a richedit control.
'Open the Task_Information table to look for Task Notes
Set rs = CurrentDb.OpenRecordset("Task_Information", dbOpenTable)
'Enumerate across the recordset looking for notes
With rs
Do While Not .EOF
If !Reserved_hasnotes <> 0 Then
' Extract string
strData = StrConv(rs!Reserved_BinaryProperties, vbUnicode)
' Put binary column data into text string
intSize = GetIntegerFromPosition(strData, 1)
' Size starts at position 1
intOffset = GetIntegerFromPosition(strData, 5)
' Offset starts at 5
strExtracted = Mid(strData, intOffset + 1, intSize)
Debug.Print strExtracted
End If
.MoveNext
Loop
.Close
End WithEnd Sub
Function GetIntegerFromPosition(s As String, p As Long) As Integer Dim i As Long, intResult As Integer For i = p + 3 To p Step -1 intResult = intResult * 255 ’ Shift one byte intResult = intResult + Asc(Mid(s, i, 1)) Next i GetIntegerFromPosition = intResult End Function
Getting the Names of Sharer Files:
Obtaining the names of sharer files from a resource pool stored in a database requires some programmatic manipulation of the data, which can be accomplished with the following code sample if you are working in Microsoft Access Basic:
Option Compare Database Option Explicit
Sub getSharers() Dim db As Database Dim rs As Recordset Dim bytBuffer() As Byte Dim strData As String, strExtracted As String Dim intSize As Integer, intOffset As Integer
’This macro will look for Pool projects and extract the name of the ’sharers (which will be semicolon delimited).
’Open the Project_Information table to look for Pool projects Set rs = CurrentDb.OpenRecordset(“Project_Information”, dbOpenTable)
‘Enumerate across the recordset looking for notes With rs Do While Not .EOF If !ResourcePool <> 0 Then ’Extract string strData = StrConv(rs!Reserved_BinaryProperties, vbUnicode)’ This is the trick you have to do to extract ANSI…. intSize = GetIntegerFromPosition(strData, 1) ’ Size starts at position 1 intOffset = GetIntegerFromPosition(strData, 5) ’ Offset starts at 5 strExtracted = Mid(strData, intOffset + 1, intSize) Debug.Print strExtracted
End If
.MoveNext
Loop
.Close
End WithEnd Sub Function GetIntegerFromPosition(s As String, p As Long) As Integer Dim i As Long, intResult As Integer For i = p + 3 To p Step -1 intResult = intResult * 255 ’ Shift one byte intResult = intResult + Asc(Mid(s, i, 1)) Next i GetIntegerFromPosition = intResult End Function
Retrieving Workgroup Message Status:
The task and resource workgroup message status fields (Update Needed, Confirmed, Response Pending, and TeamStatus Pending) are not saved out to the database, so if you want to obtain status information about messages that have been sent out to the team, you have to extract the information from the assignment fields. By looking at all of the associated assignment records for a given task or resource, the values of the task or resource status can be determined.
For example, to determine if a task has been confirmed, you must check the Confirmed field of all assignments on that task. If the Confirmed field for every assignment is True, then Confirmed for the task is True. To determine if an update is needed on a task, you must check the UpdateNeeded field of all assignments on that task. If any one of the assignment UpdateNeeded fields is True, then UpdateNeeded for the task is True.
Additional query words:
Keywords : kbdta projwin kbfaq Version : WINDOWS:98 Platform : WINDOWS Hardware : x86 Issue type : kbinfo ============================================================================= Copyright Microsoft Corporation 1998.
