Linking Project to custom SQL Server db

P

Paul

Hi,

I am assessing a spec that I have been given to create a document managment
database with some task scheduling capabilities.

The database will be created in SQL Server. Essentially each document
(record) will have a review date, an owner, and a time required to complete
the review.

The spec requires some quite sophisticated scheduling (e.g. resource
levelling, reporting, calendar views and notifications). Instead of building
this into the application I would prefer to link the 'document db' to
Project.

I have read a little and my understanding is that the best way to do this
would be to create a small vb app (left running on a server) that would.

1. Use ADO to link to 'document db' and MS Project (what is the db engine
for single user project?)
2. On a regular basis check that each document's review task exists in
Project
2a If it does, update the review date ('Task must be completed by' date
and other relevant fields in Project)
2b If it does not exist, create the task in Project db and capture the
Task ID and record this against the document record.
3. Update some key details in the document db (Task start date, end date, %
complete) if required
4. I may also have to do something similar with document owners,
automatically adding them as resources and linking them to the
imported/updated task (document review) as appropriate..

Is this the best way to go about this?

Are there any documents detailing how to do this/case studies of where this
sort of thing has been done (I have downloaded the Project data structure
from MS)?

If there is a vb class module for checking/updating/inserting tasks into
Project dbs that would save me creating my own ;-)

I am a pretty competent SQL Server and VB developer with a fair amount of
experience linking the two via ADO. I have very basic Project skills and
ideally the solution will not require me to code anything in Project itself.

As a very rough estimate to put on my assessment, I would give the above 5
days to implement (erring on the side of caution as I am unfamiliar with
working with the Project back end). Does this sound realistic (I think only
a few details will need to pass back and forth between the databases)?

Thanks

Paul
 
G

Gérard Ducouret

Hello Paul,

Just as a first step, have a look at this VBA Procedure which reads a SQL
Server data base and picks up some piece of information about the projects.

Hope this helps,

Gérard Ducouret

Dim cn As Object
Dim rst As Object
Dim rst_Task As Object
Dim Cmd As Object

Const DSN = "X58" '"NomDSN"
Const STR_USER_IDENT = "DSN" '"NomIdentificationDSN"
Const STR_PASSWORD_IDENT = "xxx" 'PassWord
'


Sub Lecture()
Dim sQuery As String
Dim i As Integer
Dim oTache As Object

For Each oTache In ThisProject.Tasks
oTache.Delete 'Supprime toutes les lignes
existantes
Next

' Ouverture de connection à SQL Server par l'intermédiaire d'un DSN
système
Set cn = CreateObject("ADODB.Connection")
cn.Mode = adModeRead 'adModeReadWrite (Lecture seule!)
cn.ConnectionString = "DSN=" & DSN & ";UID=" & STR_USER_IDENT & " ;PWD="
& STR_PASSWORD_IDENT & ";"
cn.Open

' Récupération des enregistrements de la table des projets de la base de
données pointée par le DSN
' 1ere interrogation :
sQuery = "SELECT * FROM MSP_PROJECTS"
Set rst = CreateObject("ADODB.Recordset")
Set rst = cn.Execute(sQuery, , adCmdText)


With rst
While Not .EOF
i = i + 1

ThisProject.Tasks.Add Name:=" "
ThisProject.Tasks(i).SetField FieldID:=pjTaskName, Value:=![PROJ_NAME]
ThisProject.Tasks(i).SetField FieldID:=pjTaskText12,
Value:=Format(![PROJ_INFO_STATUS_DATE], "dd/mm/yy")
ThisProject.Tasks(i).SetField FieldID:=pjTaskText14,
Value:=![PROJ_PROP_AUTHOR]
ThisProject.Tasks(i).SetField FieldID:=pjTaskText10,
Value:=![PROJ_PROP_MANAGER]

If IsDate(![PROJ_INFO_STATUS_DATE]) Then
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber12,
Value:=Format((Date - ![PROJ_INFO_STATUS_DATE]) + 1, "###")
End If



' Lignes à insérer pour calculer le nombre de lignes:
Dim rst_Project As Object

'2e interrogation :
sQuery = "SELECT MSP_PROJECTS.PROJ_NAME as ProjectName, count
(MSP_TASKS.Proj_ID) AS NumberOfTasks FROM MSP_TASKS INNER JOIN MSP_PROJECTS
ON MSP_TASKS.PROJ_ID = MSP_PROJECTS.Proj_ID WHERE(((MSP_TASKS.TASK_ID) Is
Not Null)) AND MSP_PROJECTS.PROJ_ID = " & ![Proj_ID] & " GROUP BY
MSP_PROJECTS.PROJ_NAME, MSP_TASKS.PROJ_ID "

Set rst_Project = CreateObject("ADODB.Recordset")
Set rst_Project = cn.Execute(sQuery, , adCmdText)
If Not rst_Project.EOF Then

ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber11,
Value:=rst_Project![NumberOfTasks]
End If
rst_Project.Close
Set rst_Project = Nothing

'3eme interrogation :
sQuery = "select TASK_ACWP, TASK_BCWP, TASK_BCWS ,TASK_WORK,
TASK_PCT_COMP, TASK_PCT_WORK_COMP FROM MSP_TASKS WHERE PROJ_ID = " &
![Proj_ID] & " AND TASK_OUTLINE_LEVEL = 0"
Set rst_Task = CreateObject("ADODB.Recordset")
Set rst_Task = cn.Execute(sQuery, , adCmdText)
If Not rst_Task.EOF Then
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber8,
Value:=rst_Task![TASK_BCWS]
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber9,
Value:=rst_Task![TASK_BCWP]
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber10,
Value:=rst_Task![TASK_ACWP]
ThisProject.Tasks(i).SetField FieldID:=pjTaskText11,
Value:=rst_Task![TASK_PCT_WORK_COMP] & "%"
ThisProject.Tasks(i).SetField FieldID:=pjTaskText13,
Value:=rst_Task![TASK_PCT_COMP] & " %"
ThisProject.Tasks(i).SetField FieldID:=pjTaskWork,
Value:=rst_Task![TASK_WORK] / 480000
End If
.MoveNext
Wend
rst_Task.Close
Set rst_Task = Nothing

.Close
End With
Set rst = Nothing


Sort Key1:="Nom", Renumber:=True
MsgBox "Fini : " & i & " plannings dans la Base X58", , "X58 : G.
DUCOURET"

End Sub
 
P

Paul

Thanks for that.

Would you suggest it is better to pull the data from SQL into Project
(instead of my plan which was to instigate from SQL Server)?

I guess having the update code in Project is better. If something has
changed (like the task due by date) project will have to recalculate the
start date before it can go into the SQL database anyway.

Gérard Ducouret said:
Hello Paul,

Just as a first step, have a look at this VBA Procedure which reads a SQL
Server data base and picks up some piece of information about the projects.

Hope this helps,

Gérard Ducouret

Dim cn As Object
Dim rst As Object
Dim rst_Task As Object
Dim Cmd As Object

Const DSN = "X58" '"NomDSN"
Const STR_USER_IDENT = "DSN" '"NomIdentificationDSN"
Const STR_PASSWORD_IDENT = "xxx" 'PassWord
'


Sub Lecture()
Dim sQuery As String
Dim i As Integer
Dim oTache As Object

For Each oTache In ThisProject.Tasks
oTache.Delete 'Supprime toutes les lignes
existantes
Next

' Ouverture de connection à SQL Server par l'intermédiaire d'un DSN
système
Set cn = CreateObject("ADODB.Connection")
cn.Mode = adModeRead 'adModeReadWrite (Lecture seule!)
cn.ConnectionString = "DSN=" & DSN & ";UID=" & STR_USER_IDENT & " ;PWD="
& STR_PASSWORD_IDENT & ";"
cn.Open

' Récupération des enregistrements de la table des projets de la base de
données pointée par le DSN
' 1ere interrogation :
sQuery = "SELECT * FROM MSP_PROJECTS"
Set rst = CreateObject("ADODB.Recordset")
Set rst = cn.Execute(sQuery, , adCmdText)


With rst
While Not .EOF
i = i + 1

ThisProject.Tasks.Add Name:=" "
ThisProject.Tasks(i).SetField FieldID:=pjTaskName, Value:=![PROJ_NAME]
ThisProject.Tasks(i).SetField FieldID:=pjTaskText12,
Value:=Format(![PROJ_INFO_STATUS_DATE], "dd/mm/yy")
ThisProject.Tasks(i).SetField FieldID:=pjTaskText14,
Value:=![PROJ_PROP_AUTHOR]
ThisProject.Tasks(i).SetField FieldID:=pjTaskText10,
Value:=![PROJ_PROP_MANAGER]

If IsDate(![PROJ_INFO_STATUS_DATE]) Then
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber12,
Value:=Format((Date - ![PROJ_INFO_STATUS_DATE]) + 1, "###")
End If



' Lignes à insérer pour calculer le nombre de lignes:
Dim rst_Project As Object

'2e interrogation :
sQuery = "SELECT MSP_PROJECTS.PROJ_NAME as ProjectName, count
(MSP_TASKS.Proj_ID) AS NumberOfTasks FROM MSP_TASKS INNER JOIN MSP_PROJECTS
ON MSP_TASKS.PROJ_ID = MSP_PROJECTS.Proj_ID WHERE(((MSP_TASKS.TASK_ID) Is
Not Null)) AND MSP_PROJECTS.PROJ_ID = " & ![Proj_ID] & " GROUP BY
MSP_PROJECTS.PROJ_NAME, MSP_TASKS.PROJ_ID "

Set rst_Project = CreateObject("ADODB.Recordset")
Set rst_Project = cn.Execute(sQuery, , adCmdText)
If Not rst_Project.EOF Then

ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber11,
Value:=rst_Project![NumberOfTasks]
End If
rst_Project.Close
Set rst_Project = Nothing

'3eme interrogation :
sQuery = "select TASK_ACWP, TASK_BCWP, TASK_BCWS ,TASK_WORK,
TASK_PCT_COMP, TASK_PCT_WORK_COMP FROM MSP_TASKS WHERE PROJ_ID = " &
![Proj_ID] & " AND TASK_OUTLINE_LEVEL = 0"
Set rst_Task = CreateObject("ADODB.Recordset")
Set rst_Task = cn.Execute(sQuery, , adCmdText)
If Not rst_Task.EOF Then
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber8,
Value:=rst_Task![TASK_BCWS]
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber9,
Value:=rst_Task![TASK_BCWP]
ThisProject.Tasks(i).SetField FieldID:=pjTaskNumber10,
Value:=rst_Task![TASK_ACWP]
ThisProject.Tasks(i).SetField FieldID:=pjTaskText11,
Value:=rst_Task![TASK_PCT_WORK_COMP] & "%"
ThisProject.Tasks(i).SetField FieldID:=pjTaskText13,
Value:=rst_Task![TASK_PCT_COMP] & " %"
ThisProject.Tasks(i).SetField FieldID:=pjTaskWork,
Value:=rst_Task![TASK_WORK] / 480000
End If
.MoveNext
Wend
rst_Task.Close
Set rst_Task = Nothing

.Close
End With
Set rst = Nothing


Sort Key1:="Nom", Renumber:=True
MsgBox "Fini : " & i & " plannings dans la Base X58", , "X58 : G.
DUCOURET"

End Sub

Paul said:
Hi,

I am assessing a spec that I have been given to create a document managment
database with some task scheduling capabilities.

The database will be created in SQL Server. Essentially each document
(record) will have a review date, an owner, and a time required to complete
the review.

The spec requires some quite sophisticated scheduling (e.g. resource
levelling, reporting, calendar views and notifications). Instead of building
this into the application I would prefer to link the 'document db' to
Project.

I have read a little and my understanding is that the best way to do this
would be to create a small vb app (left running on a server) that would.

1. Use ADO to link to 'document db' and MS Project (what is the db engine
for single user project?)
2. On a regular basis check that each document's review task exists in
Project
2a If it does, update the review date ('Task must be completed by' date
and other relevant fields in Project)
2b If it does not exist, create the task in Project db and capture the
Task ID and record this against the document record.
3. Update some key details in the document db (Task start date, end
Click to expand...
date,
%
complete) if required
4. I may also have to do something similar with document owners,
automatically adding them as resources and linking them to the
imported/updated task (document review) as appropriate..

Is this the best way to go about this?

Are there any documents detailing how to do this/case studies of where this
sort of thing has been done (I have downloaded the Project data structure
from MS)?

If there is a vb class module for checking/updating/inserting tasks into
Project dbs that would save me creating my own ;-)

I am a pretty competent SQL Server and VB developer with a fair amount of
experience linking the two via ADO. I have very basic Project skills and
ideally the solution will not require me to code anything in Project itself.

As a very rough estimate to put on my assessment, I would give the above 5
days to implement (erring on the side of caution as I am unfamiliar with
working with the Project back end). Does this sound realistic (I think only
a few details will need to pass back and forth between the databases)?

Thanks

Paul
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Status of predecessors Task 0
Auto populate value from one task to another in MS PROJECT 0
Avoiding Leads, using SVT, and Linking Tasks correctly 0
Tracking Resources as Trade Types - Rather than Individuals 1
Display weeks from a Milestone 0
Start Date of Project 0
Active Content in this file is blocked issue 1
Re-title multiple MS Project .mpp files at once 0

Top