none
Want to import SQL data into MS Project 2010 RRS feed

  • Question

  • I'm interested in using data from a SQL database to update task information in MS Project 2010. For example, I would like to be able to specify a query in my project that could update the % complete for a given task based on data stored in a SQL database.

    I do not see any way to do something like this (or even link an external data source to my project).

    Is there a way?

    Thursday, May 12, 2011 1:43 AM

Answers

All replies

  • You will need a VBA macro to do this. Ideally the database has teh Task's Unique ID saved because task names often change. The code is relatively simple, but the devil is in the detail. For example how to handle summary tasks, how to make sure no actual work gets booked in teh future and no incomplete work left in the past.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Thursday, May 12, 2011 2:36 AM
    Moderator
  • some sample code would have been great! easy to do in excel, however finding code for project  <> easy


    I finally found some and thought id help out the rest of the community!

    make sure you've correctly set up your tools /references to include one of the "microsoft activex data objects "

    Dim Conn As New ADODB.Connection 
    Dim rs As ADODB.Recordset
    Dim NewTask as Task
    
      Conn.ConnectionString = "Provider=sqloledb;Data Source=<dbserver>;Initial Catalog=<dbname>;User Id=<userid>;Password=<password>;"
      Query = "<your SQL Query>"
      Set rs = New ADODB.Recordset
    
      Conn.Open
      rs.Open Query, Conn
    
      Do Until rs.EOF
        Set NewTask = ActiveProject.Tasks.Add(rs![<dbnamefield>])
     'set other fields here.
     newTask.<tasknamefield> = rs![<dbfieldname>]
        rs.MoveNext
      Loop
      rs.Close
    
    
      Set rs = Nothing
      Conn.Close
      Set Conn = Nothing
    

     

    • Proposed as answer by wisc7 Wednesday, August 3, 2011 5:23 AM
    Wednesday, August 3, 2011 3:05 AM