Project Export to Access DB missing data

[EESweet]

Hi All,

I'm working on some custom reports for management using the ability to
export the MPP to an MDB to query the necessary data. What I've
noticed though is that while the Resource tables contain the resource
names and associated ResourceIntUID field, the corresponding
ResourceUID field in the Assignment tables is blank. Is this due to
something I'm not setting in Project prior to the export? Is there
any way I can force Project to populate this field?

Thanks,
Eric

 

[Rod Gill]

How is data being exported, because the built in save as to .mdb in 2003
doesn't save a ResourceIntUID field. 2007 doesn't save to .mdb at all

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[EESweet]

Hi Rod,

I'm using MSP 2007 and am able to export to an MDB.

I go to Reports - Visual Reports - Save Data - Save Database. Also
has the option of saving just the cubes, but I'm hoping to get a bit
more functionality out of accessing the tables directly, and find the
limitations of working with the cubes in Excel a little frustrating.
Just wish I could tie the Assignment data back to the Resource data
with a join is all.

Thanks for any help,
Eric

 

[Rod Gill]

Those cubes are purely for reporting purposes and do not hold all project
data. Further more they are over-written when the next report is run and I
don't know if they store more than one per database or just over-write the
same db each time?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com



Hi Rod,

I'm using MSP 2007 and am able to export to an MDB.

I go to Reports - Visual Reports - Save Data - Save Database. Also
has the option of saving just the cubes, but I'm hoping to get a bit
more functionality out of accessing the tables directly, and find the
limitations of working with the cubes in Excel a little frustrating.
Just wish I could tie the Assignment data back to the Resource data
with a join is all.

Thanks for any help,
Eric

 

[EESweet]

Those cubes are purely for reporting purposes and do not hold all project
data. Further more they are over-written when the next report is run and I
don't know if they store more than one per database or just over-write the
same db each time?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:http://www.projectvbabook.com


Hi Rod,

I'm using MSP 2007 and am able to export to an MDB.

I go to Reports - Visual Reports - Save Data - Save Database.  Also
has the option of saving just the cubes, but I'm hoping to get a bit
more functionality out of accessing the tables directly, and find the
limitations of working with the cubes in Excel a little frustrating.
Just wish I could tie the Assignment data back to the Resource data
with a join is all.

Thanks for any help,
Eric

Hi Rod,

On that dialog box screen you have the option of saving a particular
cube (Task, Resource, Assignment, etc) and you can save to any name/
location you like, so they are not the default cubes that are created
when you run a visual report. They don't get overwritten. There's
also the option to save the entire project as an Access database,
which is what I'm doing, and which is causing me grief because it
doesn't add the ResourceUID in the Assignment table, thus eliminating
the ability to link (join) the two tables.

Here's the macro produced when you record the two actions.

Sub ExportData()
'Can export any of the default cubes
VisualReportsSaveCube strNamePath:="C:\temp\Task Usage.cub"
'Can export the entire project as an Access database
VisualReportsSaveDatabase strNamePath:="C:\temp\ProjectName.mdb"
End Sub

 

[JulieS]

Hello Eric,

I just did a quick test with a relatively simple Project 2007 SP-2
file. I was able to save the reporting data and create a query (SQL
below) to show task name, task start, assignment work, and resource
name. The joins are on MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID and MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID.

The SQL is:
SELECT MSP_EpmTask.Name, MSP_EpmTask.Start, MSP_EpmAssignment.Work,
MSP_EpmResource.Name
FROM MSP_EpmResource INNER JOIN (MSP_EpmTask INNER JOIN
MSP_EpmAssignment ON MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID) ON MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID;

Is your project a master project with inserted subprojects by
chance?

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

Those cubes are purely for reporting purposes and do not hold all
project
data. Further more they are over-written when the next report is
run and I
don't know if they store more than one per database or just
over-write the
same db each time?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA,
see:http://www.projectvbabook.com


Hi Rod,

I'm using MSP 2007 and am able to export to an MDB.

I go to Reports - Visual Reports - Save Data - Save Database. Also
has the option of saving just the cubes, but I'm hoping to get a
bit
more functionality out of accessing the tables directly, and find
the
limitations of working with the cubes in Excel a little
frustrating.
Just wish I could tie the Assignment data back to the Resource
data
with a join is all.

Thanks for any help,
Eric

Hi Rod,

On that dialog box screen you have the option of saving a particular
cube (Task, Resource, Assignment, etc) and you can save to any name/
location you like, so they are not the default cubes that are
created
when you run a visual report. They don't get overwritten. There's
also the option to save the entire project as an Access database,
which is what I'm doing, and which is causing me grief because it
doesn't add the ResourceUID in the Assignment table, thus
eliminating
the ability to link (join) the two tables.

Here's the macro produced when you record the two actions.

Sub ExportData()
'Can export any of the default cubes
VisualReportsSaveCube strNamePath:="C:\temp\Task Usage.cub"
'Can export the entire project as an Access database
VisualReportsSaveDatabase strNamePath:="C:\temp\ProjectName.mdb"
End Sub

 

[EESweet]

Hello Eric,

I just did a quick test with a relatively simple Project 2007 SP-2
file.  I was able to save the reporting data and create a query (SQL
below) to show task name, task start, assignment work, and resource
name.  The joins are on MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID and MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID.

The SQL is:
SELECT MSP_EpmTask.Name, MSP_EpmTask.Start, MSP_EpmAssignment.Work,
MSP_EpmResource.Name
FROM MSP_EpmResource INNER JOIN (MSP_EpmTask INNER JOIN
MSP_EpmAssignment ON MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID) ON MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID;

Is your project a master project with inserted subprojects by
chance?

I hope this helps.  Let us know how you get along.

Julie
Project MVP

Visithttp://project.mvps.org/for the FAQs and additional
information about Microsoft Project











Hi Rod,

On that dialog box screen you have the option of saving a particular
cube (Task, Resource, Assignment, etc) and you can save to any name/
location you like, so they are not the default cubes that are
created
when you run a visual report.  They don't get overwritten.  There's
also the option to save the entire project as an Access database,
which is what I'm doing, and which is causing me grief because it
doesn't add the ResourceUID in the Assignment table, thus
eliminating
the ability to link (join) the two tables.

Here's the macro produced when you record the two actions.

Sub ExportData()
'Can export any of the default cubes
    VisualReportsSaveCube strNamePath:="C:\temp\Task Usage.cub"
'Can export the entire project as an Access database
    VisualReportsSaveDatabase strNamePath:="C:\temp\ProjectName.mdb"
End Sub

Hi Julie,

I've been swamped for a couple weeks now, so sorry for the late reply.
Thanks for the reply and the sample. I'll test out as soon as I can,
and let you know how it went.

Thanks again!
Eric

 

[JulieS]

You are most welcome Eric. If you have any questions, let us know.

Hello Eric,

I just did a quick test with a relatively simple Project 2007 SP-2
file. I was able to save the reporting data and create a query
(SQL
below) to show task name, task start, assignment work, and
resource
name. The joins are on MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID and MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID.

The SQL is:
SELECT MSP_EpmTask.Name, MSP_EpmTask.Start,
MSP_EpmAssignment.Work,
MSP_EpmResource.Name
FROM MSP_EpmResource INNER JOIN (MSP_EpmTask INNER JOIN
MSP_EpmAssignment ON MSP_EpmTask.TaskUID =
MSP_EpmAssignment.TaskUID) ON MSP_EpmResource.ResourceUID =
MSP_EpmAssignment.ResourceUID;

Is your project a master project with inserted subprojects by
chance?

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visithttp://project.mvps.org/for the FAQs and additional
information about Microsoft Project











Hi Rod,

On that dialog box screen you have the option of saving a
particular
cube (Task, Resource, Assignment, etc) and you can save to any
name/
location you like, so they are not the default cubes that are
created
when you run a visual report. They don't get overwritten. There's
also the option to save the entire project as an Access database,
which is what I'm doing, and which is causing me grief because it
doesn't add the ResourceUID in the Assignment table, thus
eliminating
the ability to link (join) the two tables.

Here's the macro produced when you record the two actions.

Sub ExportData()
'Can export any of the default cubes
VisualReportsSaveCube strNamePath:="C:\temp\Task Usage.cub"
'Can export the entire project as an Access database
VisualReportsSaveDatabase strNamePath:="C:\temp\ProjectName.mdb"
End Sub

Hi Julie,

I've been swamped for a couple weeks now, so sorry for the late
reply.
Thanks for the reply and the sample. I'll test out as soon as I can,
and let you know how it went.

Thanks again!
Eric