Project ID

[aj]

Hi All,
I am extracting data from multiple project plans through
VB code. All the project plans somehow always store -1 as
ProjectID in the database. How can I differentiate data
from one plan with another if the project ID is same for
all the plans. Could anyone please help me urgently.

Thanks

 

[JackD]

Did you look at the project database and specifically the MSP_PROJECTS
table?
I find it difficult to believe that multiple projects use the same project
ID.

What are the results of the query

SELECT PROJ_ID, PROJ_NAME from MSP_PROJECTS

-Jack

 

[aj]

Hi Jack,
Actually if save the project plan using odbc into sql
server using saveas, it does give unique id to all the
plans I save. But I am opening each plan thru oledb and
saving selected fields that I need into task, assignment
and resources table I have created. This is where I get
the same projectuniqueID no matter what project I extract
data from. I think this is happening because I open each
project one by one thru oledb. But this is going to create
lot of problem if can't differentiate data from all the
project plans. I need to know a key field to run a query
which is not going to duplicate the data.

Please help...

Thanks
aj

 

[JackD]

Now I see what is going on. Yes, a stand-alone file will always have a
singular ID. Since you are opening each plan one by one you can create your
own unique key right?
Whenever (however) you aggregate the data, just add in the project name or
some other identifier.

-Jack

 

[aj]

Thanks Jack!!
I already created another "Project" field in all the table
to capture the project name which I can use in the join
from all the tables. The way I am doing rightnow is when
the user clicks a button to extract, I first go and delete
the data from the tables before repopulating them so that
the data is not duplicated in the tables. But the big
question is, I want to tell the query to delete only
certain data not all because the tables will have data
from multiple project plans. This seems like I will have
fire a query first to capture the project name and then
fire another query to delete all the data if the project
name is same in the selected file ready to be extracted
and in the database. To me it does not seem to be the
ideal way, unless you know a better way, please let me
know. But I think the worst part would be if somebody
changes the project name. In that case my query will fail.
Please help.

Thanks
aj

 

[JackD]

Consider storing your files in a project database.
It will make this sort of multiproject querying much easier.

-Jack

 

[aj]

Thanks Jack!!
Does this mean I will have to set up project database
server?? If so then there will a licensing issue right???
In the mean time do you think I am doing the right way as
I explained earlier. Please let me know.

Thanks
AJ

 

[aj]

Hi Jack,
Another question, if I need to save the project plans in
the Project database as you mentioned then I will have to
change my code to do save as. Do you have any idea how do
you do that??

Thanks
aj

 

[JackD]

You can save all the files in a typical SQL server database without using
project server.
Do a save as, then click the ODBC button and select the SQL server you want
to use (may require going through the wizard to create a dsn)

You will need to change your code. Run the macro recorder while doing a save
to get the correct syntax.

Also read the document titled projdb.htm. Find it on your HD or on your
project CD. Read it a few times.

-Jack

 

[aj]

Hi Jack,
Could you please tell me where can I capture data for
assignment remaining work like I capture other fields from
AssignmentbyWeek table. I need to show the remaining work
besides the actual work field.

Thanks
aj