Using MS Data Shaping with OLE DB - data from more than one table in mpp file
Venkata,
You beauty! I haven't seen the SHAPE functionality before. Have taken the
liberty of playing with the code. The following example works in Project
2000 by referencing ActiveX Data Objects 2.5 Library.
I have added a third layer so as to get project level data as well. It runs
noticeably faster if you only select the fields you actually need rather
than use *.
Thanks for this, I was wondering what the 'simple' solution was!
Public Sub GetData()
Dim strSQL As String
Dim cn As New ADODB.Connection, rsPrj As ADODB.Recordset, rsTsk As
ADODB.Recordset, rsAsn As ADODB.Recordset
Set rsPrj = New ADODB.Recordset
Set rsTsk = New ADODB.Recordset
Set rsAsn = New ADODB.Recordset
Const strConn As String = "Provider=MSDataShape.1;Extended
Properties='Project Name=C:\MyProject.mpp';" _
& "Persist Security Info=False;Data Source='';User ID='';Initial
Catalog=C:\MyProject.mpp;Data " _
& "Provider=MICROSOFT.PROJECT.OLEDB.9.0"
cn.Open strConn
strSQL = "SHAPE {SELECT Project, ProjectTitle FROM Project} " _
& "APPEND ((SHAPE {SELECT * FROM Tasks}" _
& "APPEND ({SELECT * FROM Assignments} as rsAsn " _
& "RELATE 'TaskUniqueId' TO 'TaskUniqueId')) as rsTasks
" _
& "RELATE 'Project' TO 'Project')"
rsPrj.Open strSQL, cn
Do While Not rsPrj.EOF
Set rsTsk = rsPrj!rsTasks.Value
Do While Not rsTsk.EOF
Set rsAsn = rsTsk!rsAsn.Value
Do While Not rsAsn.EOF
Debug.Print rsPrj("ProjectTitle"),
Left$(rsTsk("TaskName") & Space$(30), 30), rsAsn("AssignmentResourceName")
rsAsn.MoveNext
Loop
rsTsk.MoveNext
Loop
rsPrj.MoveNext
Loop
End Sub
--
Visit www.mvps.org/project/ for the Project MVP's FAQ and third party
add-ons
Rod Gill
Microsoft Project MVP
Visit www.projectlearning.com for customized Project and Office VBA macros
and Project companion software
Thanks Rod
In that case the best option is to use hierarchial recordset using shaping.
The following example joins the "tasks" and "assignments" tables of mpp
file using msdatashape/project ole db provider and lists in debug window the
rsources assigned for each task.
Public Sub GetData()
Const strConn As String = "Provider=MSDataShape.1;Extended
Properties='Project Name=c:\projectdocuments\myproject.mpp';Persist Security
Info=False;Data Source='';User ID='';Initial
Catalog=c:\projectdocuments\myproject.mpp;Data
Provider=MICROSOFT.PROJECT.OLEDB.9.0"
Dim strSQL As String
Dim cn As New Connection, rs1 As New Recordset, rs2 As New Recordset
cn.Open strConn
strSQL = "SHAPE {SELECT TaskUniqueId FROM Tasks} APPEND ({SELECT
TaskUniqueID, ResourceUniqueID FROM Assignments} RELATE 'TaskUniqueId' TO
'TaskUniqueId')"
rs1.Open strSQL, cn
Do While Not rs1.EOF
Debug.Print rs1.Fields(0)
Set rs2 = rs1.Fields(1).Value
Do While Not rs2.EOF
Debug.Print , rs2.Fields(1)
rs2.MoveNext
Loop
rs1.MoveNext
Loop
End Sub
Regards
Venkata Krishna
Hi,
Read Office\1033 folder\OLE_DB>HTM
One limitation of reading from .mpp files is:
a.. Multi-table queries are not supported: You must use a separate query for
each table you wish to access.
--
Visit www.mvps.org/project/ for the Project MVP's FAQ and third party
add-ons
Rod Gill
Microsoft Project MVP
Visit www.projectlearning.com for customized Project and Office VBA macros
and Project companion software
Using the same principle, the following example gets inner joined data from
msp_projects and msp_tasks tables of access project data base.
Public Sub GetData()
Const strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\ProjectDocuments\ProjectData.mdb;Persist Security Info=False"
Dim strSQL As String
Dim cn As New Connection, rs As New Recordset
cn.Open strConn
strSQL = "select msp_projects.proj_id, msp_tasks.task_id from
msp_projects inner join msp_tasks on msp_projects.proj_id =
msp_tasks.proj_id"
rs.Open strSQL, cn
Do While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1)
rs.MoveNext
Loop
End Sub
Regards
Venkata Krishna
Brian
You use OLEDB commands in MS Project, just like as you would use them
anywhere else. For example, the following example retrieves combined inner
joined data from customers and orders tables of northwind SQL server
database and prints results in Project vbe editor's debug window. Set
reference to ADODB library first.
Public Sub GetData()
Const strConn As String = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=computer1"
Dim strSQL As String
Dim cn As New Connection, rs As New Recordset
cn.Open strConn
strSQL = "select customers.customerid, orderid from customers join
orders on customers.customerid = orders.customerid"
rs.Open strSQL, cn
Do While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1)
rs.MoveNext
Loop
End Sub
Regards
Venkata Krishna
Proj 2000
Is anyone using or have any example code - using data shaping with OLEDB
to
access data from more than one related table direct from MPP file. I have
some access and sql samples but no working MS Project data shaping samples
where more than one table is involved.
Brian
