Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Updating information with through SDK with MS Access

12 replies [Last post]
Walter Tarr
User offline. Last seen 8 years 50 weeks ago. Offline
Joined: 4 Dec 2007
Posts: 10
Groups: None
I have Primavera 6.1, I’m using SDK, I’m a superuser. I can link to the tables in primavera in Access, for some reason when I use an update query, it’s not letting me update the table (non ID values) The table is OAUSER_TASK that it’s linked to, and I’m updating the remaining_duration activity. The error is saying that I’m trying to update to a non-updatable query... then if i just try to type in the table, it states that the table is read only... but I’m a superuser... so confused... am i trying to write to the wrong table? Do I link to the task table differently to update?

Replies

Walter Tarr
User offline. Last seen 8 years 50 weeks ago. Offline
Joined: 4 Dec 2007
Posts: 10
Groups: None
Shiv,

I don’t think that will be a problem, it’s essentially similar to what I do now... the best would be just to attach a "test" database..
Barrie Callender
User offline. Last seen 46 weeks 4 days ago. Offline
Joined: 15 Feb 2006
Posts: 39
Groups: None
Ronald

You’ll find that Claim Digger still uses the Java API. Also, it wasn’t originally written by Primavera, but by one of their PARs (in Germany I think).

Having seen it in use since 4.1, I’m not sure about the bit about "had nothing but problems with users crashing".

They haven’t integrated it into the PM Code either. They’ve just put a hook in it to call Claim Digger.
Ronald Winter
User offline. Last seen 3 years 2 weeks ago. Offline
Joined: 4 Jan 2003
Posts: 928
Groups: None
I have a question. If the Primavera API is so good, then why did Primavera itself abandon it with Claim Digger. Primavera wrote Claim Digger using the API and had nothing but problems with users crashing and lack of multi-project support. P6 introduces a new Claim Digger; completely re-written and integrated into the PM code instead of being an API applet.

Now ask yourself, do you want to commit to a ‘technology’ that wasn’t good enough for Primavera?
shiv rao
User offline. Last seen 11 years 34 weeks ago. Offline
Joined: 10 Mar 2008
Posts: 62
Walter ,

I hope taht you find teh solution .
Can you make a document how to do this with some screen shot printouts .
It would a great docs for some one to use in future .

In fact i want to do that , but i am confused how it will work?

The end point is that can we update simultanously 4 people in excell file rather then updating the project in P6?

If we are able to get results its great.

Hope you will do it ? It will take some time of your but it would be a good OPA .

Thanks for ouyr understanding.

Regards

Shiv Rao
Walter Tarr
User offline. Last seen 8 years 50 weeks ago. Offline
Joined: 4 Dec 2007
Posts: 10
Groups: None
All,

By the way, an even "Quicker" way then mentioned below for updates is creating a "Project Update" Update table run prior to the updates themselves. That is, summarizing the "Project ID" fields for all the projects that you want to update, then creating an update table setting access_level: 1 and scope_level 7 in that table. Primavera then stores these values for some time period.... (not sure how long) you can then run a second update table so that only the projects being updated will be open to update (which saves a considerable amount of time) Both pass-through tables and linked tables are effected by the scope_level:7 which essentially opens the projects. This may be basic for most, but it really helped in speed updating issues.
Walter Tarr
User offline. Last seen 8 years 50 weeks ago. Offline
Joined: 4 Dec 2007
Posts: 10
Groups: None
David,

*sigh* yes, with the new "Named User" instead of concurrent user access, I have become cynical of Primavera...

What you say is true, API is cheaper if you would be looking at an equal number of users... However, the argument would be that SDK you only need 1 user... the database "Creator" to upload and download data into a seperate database... that seperate database then (let’s say MSAccess) being already on everyone’s computer is using existing software to run updates/modifications. So no need for "named" API user access (with named licenses, and API access being part of the "priveledges" as a function of the names liceneses). A single SDK user would be all that’s necessary.... (which is much better than the $1500/named user Web Access updates)... or the $4-5k per named client user which would be needed for each API access...
David Kelly
User offline. Last seen 1 year 34 weeks ago. Offline
Joined: 19 Oct 2004
Posts: 630
Walter,

Goodness, such cynicism in one so young!

Actually you may only use the SDK if you are a licenced user of the PM Windows client - so every user of the SDK should be paying $1500.00

The API is free to use for the same number of named users that you have a PM licence for - BUT they do not need to be the same users.

In addition it is possible to purchase additional API licences for a fraction of the PM licence cost.

So its MUCH cheaper to use street-legal API than SDK.
Walter Tarr
User offline. Last seen 8 years 50 weeks ago. Offline
Joined: 4 Dec 2007
Posts: 10
Groups: None
By the way... in my opinion... the only reason Primavera is pushing the API/Java tool... is because that way they have better control over selling "named license" tools... at $1500/pop for web access... vs. free excel spreadsheets and one SDK update database.... hmm... it’s no wonder Primavera is abandoning SDK...
Walter Tarr
User offline. Last seen 8 years 50 weeks ago. Offline
Joined: 4 Dec 2007
Posts: 10
Groups: None
Okie, thanks for the encouragement :P I figured it out by ripping apart the SQL programming on the SDK Excel spreadsheets they provided... If anyone is attempting the same thing...you have to bring OAUSER_PROJECT into the update query (the link will be automatic to whatever other table you are trying to update), then update the "access_level"field in OAUSER_PROJECT to 1 and the "scope_level"field in the same table to 7. With that you can update nearly all non-calculated fields (that is, except for historical data, which is really short-sighted of the primavera folks).

There is probably a quicker SQL statement simply to set all the projects that you want to update to 1 and 7 in access_level and scope_level, but the above works fine for now....
David Kelly
User offline. Last seen 1 year 34 weeks ago. Offline
Joined: 19 Oct 2004
Posts: 630
Walter,

You have no idea of the minefield you have walked into. This is not like using ODBC to update the old P3.

As a company, developing interfaces to P6 is a big part of our work. We would never, ever use the SDK. The API is the only robust fully featured bug free way to do it. That requires fully-fledged JAVA skills.

In the "Old days" a smart planner could build toolsets to update P3 under-the-hood. This is very much the preserve of IT professionals with P6.
Walter Tarr
User offline. Last seen 8 years 50 weeks ago. Offline
Joined: 4 Dec 2007
Posts: 10
Groups: None
I have all the projects "open" thus i have all the tables accessable, and all the information in the tables accessable. I had already changed the INI file so that i pulled all projects information into the tables. My concern is "updating" them. I have all the information below and try to update but I’m get a "read-only" error, from non-calculatable fields that I’m trying to update (for examples something like task_name) where I have the TaskID, Project_ID and referemce items tied (so that the database is not trying to push multiple items into a single activity/project) but there is a 1:1 relationship. So it’s not a relationship problem, it’s not a information problem, it’s not a user access problem (since i’m a super-user), yet i’m still getting read-only error... any suggestions?
Santosh Bhat
User offline. Last seen 51 weeks 1 day ago. Offline
Joined: 15 Apr 2005
Posts: 381
Its not as simple as connecting to the tables via Access. I’m no expert but there’s a series of other steps that need to occur including opening and closing projects.

Suggest you take a look at the following from Primaveras Knowledgebase:

prim11818 - How to open / close a Project through the SDK?
2002118115147 - How to load all projects when initially logging into the SDK?