none
Automate Data Import From Oracle to Project RRS feed

  • Question

  • Hi, Im looking for some sample VBA scripts that will help me import some oracle data based on a SQL query to the Gantt chart view in MS Project. Goal: I really want to click a button on the ribbon and have it connect with an Oracle database query, show a status bar during the refresh and update the data in MS Project Gantt chart view.

    Help/suggestions appreciated.

    Thanks


    • Edited by DataOnCrack Friday, February 8, 2013 7:36 PM
    • Moved by Cindy Meister MVP Saturday, February 9, 2013 8:06 AM Project-specific question from General Office Development
    Friday, February 8, 2013 7:36 PM

All replies

  • There isn't a way to import data into Project from a SQL Query in Oracle. You can however develop VBA Code to do this. It's not simple but it is easily done provided your Oracle data has an easy match to data in Project. Can't comment any further without some sample data. Most likely you need to pay someone to develop this for you.

    You will need to provide a good sample set of data, probably in Excel to begin with then provide external access temporarily to allow debugging of the code to read from Oracle.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Proposed as answer by Renke Holert Sunday, February 10, 2013 5:30 PM
    Saturday, February 9, 2013 10:49 PM
    Moderator
  • Hi Rod, I think we might of had an exchange of ideas before. I am a VB programmer. I assume that sample scripts like this are hard to find, as I have found nothing that allows me to use an OLEBD method to run a SQL query and throw the results of that query into the Gantt view of a project file.

    Doing this manually, it will only let me paste 50 rows at a time into the Gantt view when copying from Excel, paste special, text data.

    Let me know what you think my next steps are. I was trying not to order a book if this is the only thing I want to do in VBA (if that makes sense).

    Thanks

    Monday, February 11, 2013 9:08 PM
  • If you are in in VB.Net you might be able to use LINQ statements to query the oracle DB directly from your VB.net code and place them into the right fields in Project. then put the whole thing into an Addin for Project.

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Monday, February 11, 2013 9:51 PM
    Moderator
  • Hello,

    I've had to make some assumptions in composing this response:

    • You only want one-way import, creating a new schedule from the data in Oracle.
    • There is no need to update an existing schedule with revised data from Oracle
    • You won't be importing timephased data from Oracle (that's a level of complexity I've not got my head around).

    I've done similar in the past (purely with VBA, not VB.Net add-in). I can't share the code in its entirety due to IP issues (and I'm not sure it would help as the requirements were a bit different), but at a high level here's how I would approach it:

    • You'll want to create 3 classes for the entities you'll be importing from Oracle: (ora_Resources, ora_Tasks and ora_Assignments) with properties for all the values Oracle gives you for each.
    • Additionally, each entity will need a property to link to its corresponding object in MSP (once created), so for example you'll have an ora_Task object with the property ora_Task.corresp_MSP_Task of the type Task
    • The Assignment class should define the object with properties of teh type ora_Task and ora_Resource to contain references to the objects which it relates to.
    • Now to populate collections uses the object types defined in these classes. I've queried SQL via ODBC before using Microsoft ActiveX Data Objects 2.8 Library. Here's a sample of how I've got this working previously:
    'Defining variables used to capture SQL data
        Dim sql_Proj_ID As Long
        Dim sql_Qry_Results As recordset
        Dim sql_ARMIMPORT_db As New ADODB.Connection
        Dim sql_Command As ADODB.Command
        Dim sql_Parameter As New ADODB.Parameter
        
        Dim x As Integer
        
        Dim int_TaskCount
        
    'Set up the connection to the ARMIMPORT sql server
        sql_ARMIMPORT_db.Open "XXXX", "XXXX", "XXXX"
            
        Set sql_Qry_Results = New ADODB.recordset
        sql_Qry_Results.CursorLocation = adUseClient
        sql_Qry_Results.CursorType = adOpenStatic
        sql_Qry_Results.LockType = adLockReadOnly
    If UpdateExisting = True Then
        'Get the project number from the    'Get the project number from the project name
            sql_Qry_Results.Open "SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_NAME = '" & ProjName & "'", sql_ARMIMPORT_db
     
    • We'll need a query for each entity: do tasks/resources first, and assignments last (to make sure you can populate the ora_Tasks and ora_Resources property for assignments.

    • Once you've got all three tables into VBA collections using the three classes, it's a case of looping through the collections and creating new tasks, resources and assignments for each (again, do assignments last).

    Hope this gives you a good idea of the direction you need to take, but if you've got specific questions about any step in the above, please shout.


    Tuesday, February 12, 2013 12:05 PM
  • Au contraire my friend, there are many, many code samples of reading data from SQL using OLEDB. You will need query each for Tasks, Resources and Assignments. As Andrew says, once you have those recordsets you will need to loop through them adding tasks, resources and assignments one at a time. For a 1000 task project with many assignments, it will take some time. It may even be quicker to create a .xml file and then read that into project in one go.

    But you must buy a book: the only Project VBA book just happens to be mine, so I insist!


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, February 13, 2013 7:56 AM
    Moderator