USING AUTOMATION TO DRIVE ACCESS FROM MICROSOFT PROJECT

Driving Access from other Office applications is about as easy as manipulating Access objects from within Access itself. This chapter discusses how to drive Access from Project. To manipulate Access objects from any other application, you need to understand the Data Access Objects model (see Figure 13.13). When working with Access objects such as reports and forms, you will want to have a reference to the Microsoft Access 9.0 Objects Library. When you work with tables and queries, depending on whether you are using DAO or ADO, you will have a reference to either the Microsoft DAO 3.6 Object Library for DAO or one or more of the three libraries for ADO. For a list of these libraries, see Chapter 6, “Introducing ActiveX Data Objects.”

Figure 13.13. The Data Access Objects model.


The AutomateToAccess routine is located in a Microsoft Project project called Access.mpp, which can be found on the CD-ROM in the ExamplesChap13 folder. This routine performs the following steps:

1.
It opens the database where you want to create the table—for this example, Chap13.mdb. This table, tblProjects, will be used to hold information about a project.

2.
AutomateToAccess calls the CreateProjectTable routine, which creates the Projects table; then AutomateToAccess appends the Projects table to the database's TableDefs collection.

3.
The AutomateToAccess subroutine opens the Projects table in Access and cycles through each task, assigning the Project-specific fields.

4.
The subroutine closes the recordsets.

Listing 13.6 shows the actual code that performs these tasks.

Listing 13.6. Access.mpp: Creating an Access Table While in Microsoft Project
'Macro AutomatetoAccess
'
Sub AutomateToAccess()

   '-- Be sure to set your references
   Dim dbProjDemo As Database
   Dim dynProjects As Recordset

   Dim tskTasks As Task
   Dim ResResources As Resource

   On Error GoTo Error_AutomateToAccess

   '-- Open the sample mdb
   Set dbProjDemo = OpenDatabase(ActiveProject.Path & "Chap13.mdb")

   On Error Resume Next
   dbProjDemo.Tabledefs.Delete "tblProjects"
   On Error GoTo Error_AutomateToAccess

   CreateProjectTable dbProjDemo

   '-- Open the Projects table
   Set dynProjects = dbProjDemo.OpenRecordset("tblProjects")

   '-- Write resource and task info to the appropriate fields
   '-- Note that when dealing with Access object the bang is supported.
   For Each tskTasks In ActiveProject.Tasks
      With dynProjects
          .AddNew
              !Tasks = tskTasks.Name
              !Predecessors = tskTasks.Predecessors
              !Start = tskTasks.Start
              !Duration = tskTasks.Duration / 480  'proj calcs in minutes
              !Resources = tskTasks.ResourceNames
          .Update dbUpdateRegular, False
      End With

   Next tskTasks

   dynProjects.Close
   dbProjDemo.Close
   MsgBox "The table 'tblProjects' has been created " & _
          "in the database 'Chap13.mdb'."
   Exit Sub

Error_AutomateToAccess:
    MsgBox Err.Description
    Exit Sub

End Sub

Note

The ! (bang) character is used with the Access objects. Not all Office applications use the bang character for referencing objects.


The following line of code uses the OpenDatabase method from Access combined with the object ActiveProject's Path property used in Project:

Set dbProjDemo = OpenDatabase(ActiveProject.Path & "Chap13.mdb")

This line opens an Access database from within Project. (Is that power, or what?)

To run this code in the project itself, open Access.mpp in Microsoft Project, and you see the message shown in Figure 13.14.

Figure 13.14. The VBA code in Access.mpp creates a table from a project, storing tasks and resources.


To examine the code for the AutomateToAccess subroutine, choose Macros from the Tools menu. You can then select the AutomateToAccess macro and click Edit.

One last thing to look at before moving on is the CreateProjectTable subroutine. Just by looking at Listing 13.7, you can't tell whether it was written in Access or Project (for the record, it was written in Project). That's one of the beauties of working with VBA.

Listing 13.7. Access.mpp: Creating the Structure of an Access Table
Sub CreateProjectTable(dbProjDemo As Database)
   Dim tdfProjects As TableDef
   Dim fldNewField As Field
   '-- Create a table
   Set tdfProjects = dbProjDemo.CreateTableDef("tblProjects")

   '-- Create the tasks field
   Set fldNewField = tdfProjects.CreateField("Tasks", dbText, 50)
   tdfProjects.Fields.Append fldNewField

   '-- Create the resources field
   Set fldNewField = tdfProjects.CreateField("Resources", dbText, 30)
   fldNewField.AllowZeroLength = True
   tdfProjects.Fields.Append fldNewField

   '-- Create the predecessors field
   Set fldNewField = tdfProjects.CreateField("Predecessors", dbText, 30)
   fldNewField.AllowZeroLength = True
   tdfProjects.Fields.Append fldNewField

   '-- Create the start field
   Set fldNewField = tdfProjects.CreateField("Start", dbText, 30)
   tdfProjects.Fields.Append fldNewField

   '-- Create the Duration field
   Set fldNewField = tdfProjects.CreateField("Duration", dbText, 30)
   fldNewField.AllowZeroLength = True
   tdfProjects.Fields.Append fldNewField

   '-- Append the new table
   dbProjDemo.Tabledefs.Append tdfProjects
End Sub

Figure 13.15 shows the tblProjects table created by the Access.mpp routines.

Figure 13.15. The Projects table contains data read from a Microsoft Project project.


Going from other Office products to Access is as easy as going from Access to others. It's just a matter of knowing which Data Access Objects to use, as well as which objects and methods to use with VBA. Now it's time to check out how to drive one of the newest members of the Office products—Outlook—from Access.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.196.175