Proj41: Using DAO to Export Data to Microsoft Access
ID: Q151555
|
The information in this article applies to:
-
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
REFERENCES
"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.
Additional query words:
4.10a
Keywords : kbcode kbprg
Version : WINDOWS:4.1,4.1a
Platform : WINDOWS
Issue type : kbhowto