S
Shane Garlick
There has been a lot of discussion in the past about linking Project data within an MS Access database. You would think that, both being Microsoft databases, the ability would be there. However, due to the complexity of the Project data, making it accessible in Access would be catestrophic to your schedules.
However, using OLE DB and ADO, it's possible to extract data from a Project file and place it into an Access database. Keep in mind, this doesn't create a link. The data needs to be refreshed using VBA, but writing a sub process that can be run from a button on a form will make the process easy. Also, because of how this works, this code needs to reside within a database file other than the one that will contain the data.
Here is the code to connect to the Project file and the Access file (be sure that the references within the VB Editor refer to ADO, not DAO):
Sub DataConnect()
Dim cnDB1 as ADODB.Connection, RS1 as Recordset
Dim cnDB2 As ADODB.Connection, RS2 As Recordset
'This creates a recordset of the table that will collect the data
Set cnDB2 = CreateObject("ADODB.Connection")
cnDB2.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = C:\AccessFile.mdb"
cnDB2.Open
Set RS2 = New ADODB.Recordset
RS2.CursorType = adOpenDynamic
RS2.LockType = adLockOptimistic
RS2.Open "tTable", cnDB2, , , adCmdTable
'This connects to the Project file
Set cnDB1 = CreateObject("ADODB.Connection")
cnDB1.ConnectionString = "Provider = Microsoft.Project.OLEDB.11.0;" _
& "Project Name = C:\ProjectFile.mpp"
cnDB1.Open
strSQL = "SELECT * FROM Table"
RS1.Open strSQL, cnDB1
End Sub
This code will create two recordsets, one of a Project File table and one for the Access table (the code needs to be refined to point to the right files and the right tables). The Access recordset is updatable, the Project recordset is a simple SELECT statement. You can now pull data from the project file table and place it into the Access table.
For a complete list of MS Project OLE DB tables, refer to the PJOLEDB.htm file found on your MS Project disk.
Hope this helps those who what to share Project data within an Access database. If you're new to VBA programming, I'd get a little more familiar with ADO and how it works before I attempt to do this.
However, using OLE DB and ADO, it's possible to extract data from a Project file and place it into an Access database. Keep in mind, this doesn't create a link. The data needs to be refreshed using VBA, but writing a sub process that can be run from a button on a form will make the process easy. Also, because of how this works, this code needs to reside within a database file other than the one that will contain the data.
Here is the code to connect to the Project file and the Access file (be sure that the references within the VB Editor refer to ADO, not DAO):
Sub DataConnect()
Dim cnDB1 as ADODB.Connection, RS1 as Recordset
Dim cnDB2 As ADODB.Connection, RS2 As Recordset
'This creates a recordset of the table that will collect the data
Set cnDB2 = CreateObject("ADODB.Connection")
cnDB2.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = C:\AccessFile.mdb"
cnDB2.Open
Set RS2 = New ADODB.Recordset
RS2.CursorType = adOpenDynamic
RS2.LockType = adLockOptimistic
RS2.Open "tTable", cnDB2, , , adCmdTable
'This connects to the Project file
Set cnDB1 = CreateObject("ADODB.Connection")
cnDB1.ConnectionString = "Provider = Microsoft.Project.OLEDB.11.0;" _
& "Project Name = C:\ProjectFile.mpp"
cnDB1.Open
strSQL = "SELECT * FROM Table"
RS1.Open strSQL, cnDB1
End Sub
This code will create two recordsets, one of a Project File table and one for the Access table (the code needs to be refined to point to the right files and the right tables). The Access recordset is updatable, the Project recordset is a simple SELECT statement. You can now pull data from the project file table and place it into the Access table.
For a complete list of MS Project OLE DB tables, refer to the PJOLEDB.htm file found on your MS Project disk.
Hope this helps those who what to share Project data within an Access database. If you're new to VBA programming, I'd get a little more familiar with ADO and how it works before I attempt to do this.