export of linkages between objects into MS project

[bluemew]

I want to export into MS project to display the objects as elements in a
Gantt-type view. Managed to achieve this for the objects themselves (using
the MS project Export Map) however i'm struggling to figure out how to import
the linkages

In order to import the linkages automatically, I think MS project expects a
text field containing the ID's of the linked objects (for import into the
"predecessors" field) separated by commas, eg "1,2,6,10".

For some context, The database contains 2 tables "research activities" and
"production activities" linked via a junction table (many-to-many
relationship - each research activity can link to many production activities,
and each production activity can have many research activities

Any clues on how to set up a query/expression to create this as some sort of
calculated field? not too familiar with SQL or VBA, so the simpler the better
;-)

Alternatively, Anyone got a better method for exporting links into MS Project?

 

[John Nurick]

Hi Blue,

I've no experience moving stuff from Access to Project. But here's a
function that Duane Hookom posted a while ago to concatenate values from
related records into a comma-separated string. It works in a query.
Also, there are a couple of less versatile but perhaps simpler
concatenation functions on the Access Web www.mvps.org/access .

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strConcat As String 'build return string
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len (pstrDelim))
End If
Concatenate = strConcat
End Function

 

[bluemew via AccessMonster.com]

Thanks John, I'll give it a try... looks like exactly what i'm looking for.
Cheers!

 

[bluemew via AccessMonster.com]

John, thanks, the function works fine (after a few too many hours scratching
around trying to set up modules and sql queries etc...was particularly
frustrated to find that naming the module after the function within it
stopped it from working!!! Anyway...

Currently, my query reads

SELECT DISTINCT CWCompID AS Expr1,
Concatenate("SELECT RTProgID FROM RT2Comps _
WHERE ExploitID =" & [CWCompID]) AS RTProgID
FROM RT2Comps;

This returns Expr1 (the value of CWCompID entered into the dialogue box) with
a string listing the associated R&T programme ID's (eg 1,2,45,67) in my query.
Which is great. But...

I'd like the query to step through each value of CWCompID, and store each
resulting concatenated string as a field in CWComp - any ideas on how to
build on this? Do I need to use a macro?

 

[John Nurick]

I assume CWComp is a table.

As a rule one wouldn't want to store the concatenated values in a table,
because doing so disconnects the concatenated values from the records
they are concatenated from, which is usually not a good idea. Instead,
one would use a query to generate the concatenated values on the fly.

Anyway, if Project can import data from an Access query, just do that.
If Project needs a text file, export your query to a text file and
import that.

John, thanks, the function works fine (after a few too many hours scratching
around trying to set up modules and sql queries etc...was particularly
frustrated to find that naming the module after the function within it
stopped it from working!!! Anyway...

Currently, my query reads

SELECT DISTINCT CWCompID AS Expr1,
Concatenate("SELECT RTProgID FROM RT2Comps _
WHERE ExploitID =" & [CWCompID]) AS RTProgID
FROM RT2Comps;

This returns Expr1 (the value of CWCompID entered into the dialogue box) with
a string listing the associated R&T programme ID's (eg 1,2,45,67) in my query.
Which is great. But...

I'd like the query to step through each value of CWCompID, and store each
resulting concatenated string as a field in CWComp - any ideas on how to
build on this? Do I need to use a macro?

 

[bluemew via AccessMonster.com]

Ok take the point about fields - yes all i want to do is export the query.

The real problem I would like to get around is that the query currently works
for one record only on the primary key side (CWCompID) - a single (user
defined) value as entered in the dialog box. I would like the query to run
for all values of CWCompID and create a concatenated list of linked objects
for each record.

My previous forays into coding (NOT using VBA/SQL) make me think that I need
to read in values of CWComp as an array, and/or set up a loop to run the
concatenate function repeatedly whilst stepping through the records, but I
dont know how to approach this in access....

 

[John Nurick]

Don't forget, I can't see over your shoulder, don't know what's in your
database, don't know the tables or fields or relationships or anything.
And you neither confirmed nor corrected my assumption that CWComp is a
table.

Possibly what you need is something like this (which assumes that CWComp
is a table whose primary key is CWCompID, and that RT2Comps is a table
in which ExploitID is a foreign key into CWComp):

SELECT CWCompID,
Concatenate("SELECT RTProgID FROM RT2Comps WHERE ExploitID =" &
[CWCompID]) AS RTProgID
FROM CWComp;

But in the absence of information from you this is just guessing.

 

[bluemew via AccessMonster.com]

John - apologies, didnt mean to be diliberately vague. I read the message
back and appreciate it was difficult to understand .

Yes CWComp is a table (with CWCompID as a primary key). linked to a junction
table with 2 fields RTprogID and ExploitID (which is foreign key correponding
to CWCompID)

Your query suggestion in the last post worked fine - i presume it was the AS..
FROM statement that I had incorrect. Looking back, I notice that your
original suggestion was in the correct format, so the answer was there all
along.

I think a bit of time developing an understanding of SQL syntax and structure
might be well spent, If i'm ever going to have to do similar things again.
Any good links that explain (for beginners) use of SQL, particularly using it
with visual basic functions? Just that ive got a history of MATLAB type
coding, which handles data quite differently


Thanks for your help.

Mike

 

[John Nurick]

I'm glad you've got it working, Mike.

Not having used MATLAB I can't comment on it, but SQL is certainly a
long way from VBA. Remember that it's basically declarative not
procedural, and basically works with (or maybe defines) sets of records
and you're part-way there; but the terminology and syntax still take a
lot of getting used to.

If you want to go to it seriously, the book SQL Queries for Mere Mortals
(Hernandez and Viescas) is very good. I don't have any links to
recommend, but there's sure to be something at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html