Proj41: Using DAO to Export Data to Microsoft Access

• Microsoft Project for Windows 95, versions 4.1, 4.1a

SUMMARY:

The following macro demonstrates how to use Visual Basic for Applications in Microsoft Project and the Microsoft DAO 3.0 Object Library to export Microsoft Project data to a Microsoft Access 2.0 or 7.0 Database (.MDB) file.

MORE INFORMATION:

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

This macro prompts for a unique file name to be entered in the format, C:\PROJECT.MDB. If the file already exists an error will occur.

When the file is successfully created, the macro will go through all the tasks on the current project and save the ID, Task name, Start, Finish and Duration fields to the database file. The macro can easily be expanded to save any of the project fields that are needed.

In a new macro module, before entering the macro code, click References on the Tools menu, and then select the Microsoft DAO 3.0 Compatibility object library

Sample Macro Code

   Sub ProjectToAccess()

   Dim projdb As Variant
   Dim projtable As TableDef
   Dim projrecset As Recordset
   Dim task As Object
   Dim filename As String

   filename = InputBox("Please enter a full filename with a .MDB _
   extension" & Chr(13) & "E.g. - C:\PROJECT.MDB", "Inputbox")

   If filename = "" Then Exit Sub

   ' dbVersion20 specifies the Microsoft Jet database engine version.
   Set projdb = CreateDatabase(filename, dbLangGeneral, dbVersion20)
   Set projtable = projdb.CreateTableDef("Project")

   projtable.Fields.Append projtable.CreateField("ID", dbText)
   projtable.Fields.Append projtable.CreateField("Task Name", dbText)
   projtable.Fields.Append projtable.CreateField("Start", dbDate)
   projtable.Fields.Append projtable.CreateField("Finish", dbDate)
   projtable.Fields.Append projtable.CreateField("Duration", dbText)

   projdb.TableDefs.Append projtable

   Set projrecset = projdb.OpenRecordset("project")

   With projrecset
        For Each task In ActiveProject.Tasks
             If Not (task Is Nothing) Then
                  .AddNew
                  ![id] = task.id
                  ![Task Name] = task.Name
                  ![start] = task.start
                  ![finish] = task.finish
                  ![duration] = task.duration
                  .Update
             End If
        Next
   End With

   projdb.Close

   set projrecset = nothing
   set projtable = nothing
   set projdb = nothing

   MsgBox "Finished Creating Database - " & filename

   End Sub

"Microsoft Jet Database Engine Programmers Guide," ISBN 1-55615-877-7

DAO.HLP, which is installed with the data access object library, provides indepth information about the techniques used in the macro.