linking to MS Project from MS Access (1 Viewer)

I can't find a way to link from MS Access to MS Project 2000 does anybody know how? Plz help.

MS Project uses OLE automation the same as the whole Microsoft family. If you set a reference to Project in your database, you can control Project from within Access as well as retrieve data from and supply data to project files.

Email if you need more specifics.

Scott,
Sorry for the delay,
but i'm really confused, I want the starting point with it and I can do it. I did at project to the references, but how do I start with it, do you have any samples, Thanks.

What are you trying to do? Provide data? Pull data? Make a chart?

Scott,
I"m trying to link data from MS Access database to MS Project, then I want to design few reports. I have ms project file which has the info, I want to use it from ms access, same as when you link ms access to ms excel to use the data. Thank you very much. I hope to get some help.

mr. moe,

I have used A2000 and MS Project together quite a bit. It has
been my experience that they do not really work together.
This thread might help:

http://www.access-programmers.co.uk...s=&threadid=47358&highlight=project+and+wayne

If you have any specific questions, please ask.

Wayne

Access to Project

Mr. Moe,

We've had some success. Mostly we read Project data into Access and then export it from there to MySQL. Reading the data was fairly easy (after a few hints from our friends, here). As far a writing back to a Project file from Access, I've written a dummy file out, but hope to do more within a month.

I would pass you the code, but it is behind a secure area and I'm not allowed to take it out. I'll print it and scan it and post the scanned version. Better yet, email it. Please let me know your email address.

chris

Chris,

Its good to see that you have successfully worked with
Project. It looks like you have been successful at reading
many projects and modeling them in Access.

That is similar to what we did. We then collected actuals
in Access, produced reports in Access and Excel. Life so
far was good.

When you write out to Project bear in mind the following:

1) You must use the Object model, do not write directly to
"Access style" tables.

2) Learn Time-Scale values if you intend to put actual cost
data back into Project

3) Expect Project to "alter" the schedule of the project,
sometimes dramatically.

4) I can provide some examples, especially TSV ...

Mr. Moe.

As you can see, this can be done. If you can express some
of your specific wants/needs then maybe Chris and I can help
you see whether these are feasable or not.

Wayne

Wayne, Chris
Thank you guys for your BIG help. I only want to link data from Access like Task, description, start and finish dates, then I want access to develop a report based on the above fields I pick from MS Project. Once I know how to link the data or even import the data, I will know what to do after. I have linked like sybase, excel on so on but MS Project is not availabe on the drop down for me to link. Your assistance is higly appreciated. Please let me know if you want me to explain little bit more. Thanks again.

Moe,

Project to Access

Wouldn't you know it, my HP scanner can't find the OCR software so I have to reload. I'll get that done as soon as I can.

We didn't do anything terribly fancy with reading resource data in. We kept is simple, same as you want to do: task, id, start, finish. However, we had 80 files that had external links manually entered in text columns, with no QC on whether the links were any good, so getting all the schedules to MySQL allowed us to create a very nice report that finds all critical path errors, etc.

Stay tuned.

Moe,

While we wait for Chris, my post a few panels back had
some code excerpts for reading from Project into Access.
For any long-term solution, I'd wait and have the luxury
of seeing Chris' code too.

I can dredge up some samples of writing an MS Project file
from Access, but the management folks using them didn't like
the distortion from the Access actuals.

Wayne

Prj 2 Access

Well, scanning didn't work so well, so I retyped. Faster, anyway.
Amazing how simple it is once it happens. It just takes forever to figure out how to do it. I repeat my disappointment with Microsoft documentation.......

Hope this helps. I didn't clean up the code, so there are a few extraneous lines.
===================================
Function ReadProjectFiles()
On error goto Err_ReadProjectFiles

Dim varFileName as Variant 'Not needed
Dim prjApp as MSProject.Application
Dim prjProject as MSProject.Project
Dim dbs as ADODB.Connection 'Not needed
Dim sked_rst as New ADODB.Recordset 'You can prebuild this table or create with code.
Dim filenames_rst as New ADODB.Recordset
Dim tsk As Object
Dim strFileName as String
Dim strPath as Path
Dim strSkedFilesTable as String
Dim strSkedTable as String
Dim intCt as Integer
Dim iProceed as Boolean

strSkedFilesTable = "tblFileNames" 'This table was loaded with .mpp files
strPath = "c:\path2files" 'Path to the files

Set prjApp = CreateObject("MSProject.Application")
prjApp.Visible = False
prjApp.Alerts (True) 'God knows why the different syntax!

filenames_rst.Open strSkedFilesTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'In case this is a reload, clear the schedule table
sked_rst "delete * from " & strSkedTable, CurrentProject.Connection
'Then open the schedule table
sked_rst.Open strSkedFilesTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

filenames_rst.MoveFirst
'Might test to be sure there are records in the table before proceeding

iProceed = True

Do While iProjceed And Not filenames_rst EOF
strFileName = filenames_rst!mpp_name
prjApp.FileOpen (strPath & strFileName)
prjApp.Visible = False
prjApp.Alerts (True) 'God knows why the different syntax!

Set prjProject = prjApp.ActiveProject

'Process all tasks in each of the mpp files

For intCt = 1 to prjProject.Tasks.Count
Set tsk = prjProject.Tasks( intCt)
sked_rst.AddNew 'Add a new record to the Access table
If tsk Is Nothing Then
'Skip blank rows
Else
sked_rst!mpp_name = strFileName 'Have to know which mpp the task is in
sked_rst!unique_id = tsk.UniqueID
sked_rst!task_id = tsk.ID
sked_rst!Task = tsk.Name
sked_rst!outline_level = tsk.Outlinelevel
sked_rst!Start = tsk.Start
sked_rst!Finish = tsk.Finish
'And so on for as many fields as you need
End if
sked_rst.Update 'Be sure to save the record
Next
prj.fileClose pjDoNotSave
filenames_rst.MoveNext 'Get the next mpp file
Loop


Exit_ReadProjectFiles:
sked_rst.Close
filenames_rst.Close
Set sked_rst = Nothing
Set filenames_rst = Nothing
Exit Function

Err_ReadProjectFiles:

dapiaoen,
Thank you so much, this looks good, I pasted the function in access, one question the tblFileNames table does that get created in ms access when I run the function. I"m still little bit confused.!!! Please help me.

Chris,
Here is my email address hamoudy10@aol.com, sorry I never noticed that you have asked for my email. Please guys my task is simple, I only need to know how to link or import data which is stored in ms project.mpp formate to an access table. The code you guys have posted is really helpful, it's just that, I can't get it. I want to link to a specific file (mpp) and then link fields. Please Please Please if you have a clue try to help me if not that's ok, the way i'm doing it now is, i'm copying all the data from ms project , pasting it in excel and then linking access to excel and then providing the reports.
Moe.

Moe,

Did you ever get this resolved?

I didn't want to butt in, but can't bear to think of you doing all
of that manual labor.

Wayne

Project to Access

Wayne,

I answered his last question by email, and offered to look at any code he had written.

The code I typed may have had a syntax error or two.

Still waiting for reply.

Chris

Guys,
Many thanks to all of you Wayne, Chris and the rest who offered help. Honstely I haven't figured out a way to do it by VB code, i'm currently still copying the data from MS Project and Paste it to excel, then link ms access to excel for the purpose of printing reports. I know that one day I will be able to fiqure it out because it's a challanging thing for me but i'm really so busy doing other things. I am a person who loves access, I have done so many things in vba in access. Again thank you for your help and support. Guys if you have an opinion or tips you would like to share please feel free to do so.

Moe,

By the way, this is what I did for Mr. Moe.

Scott,

That looks good.

If mr moe wants to embellish it with other fields, he should
have no trouble.

Wayne

Scutt,
It worked perfectly, many many many thanks to you, you are such a genius guy.

Moe,