Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL in VBA (project 2000/2002)

    Ok I see all these references to using SQL against the project database but no references to where I can use SQL in VBA which would be great for some of the advanced stuff I am doing, and it would be even better if I could use the INSERT function to add tables to the database.

    Anyone know of a reference book or web page that shows the ins/outs to this? Is it even possibe from VBA or do I have to go at it from an external program (like access or something)???

    thanks

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL in VBA (project 2000/2002)

    It is possible to connect to MS Project using VB or access. I prefer to connect using access to extract and then manipulate data. You will need to set up an odbc driver to give you access to the MS Project tables.

    I would suggest trying the Microsoft Knowledge base for using automation to attach to other programs using access. This should give you some ideas on how to connect. As to references, there are many books that review this. I use the Special Edition using Project 2000 which gets into some detail on macros and programming.

    You might also want to review some earlier posts as well.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VBA (project 2000/2002) I FOUND IT!!!!

    I found it!! I guess I was not clear in my need so I am sure the flood of answers I would have gotten from the knowledgable people on this site didn't happen.

    I wanted to connect to my PROJECT 2002 DATABASE that is stored in SQL 7.0 on a WINDOWS 2000 Standard Server (go to to datacenter next month)...

    Here is example code from Microsoft Help that connects: I will just modify it for what I want to do:

    Sub getRtf()
    'This macro extracts RTF data from MSP_TASKS.TASK_RTF_NOTES. This data can then be written
    'to a file that can be opened with Microsoft Word or displayed in a richedit control.

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String, rtf As String, cnString

    'Open the MSP_TASKS table to look for TASK_RTF_NOTES
    cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:tempMyProject.mpd"
    sql = "select PROJ_ID, TASK_UID, TASK_RTF_NOTES " & _
    "from MSP_TASKS " & _
    "where TASK_RTF_NOTES is not null" 'can specify a specific PROJ_ID and TASK_UID instead
    cn.Open cnString
    rs.Open sql, cn

    'Enumerate across the recordset looking for notes
    With rs
    Do While Not .EOF
    rtf = StrConv(.Fields("TASK_RTF_NOTES"), vbUnicode) ' Put binary column data into text string
    Debug.Print rtf
    .MoveNext
    Loop
    .Close
    End With
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •