MS Access on the Fly
Posted by Derek Mang on 22nd April 2008
MS Access databases are great for quick data synthesis applications. The ability to create, populate, and then extract data can really simplify situations where consolidation of information is the eventual goal. A little while back, we put in place a tool that makes life easier for our project management office function. The following describes the situation and how MS Access was leveraged.
Situation
Every two weeks, our management team meets to discuss ongoing projects. Input to this discussion is a report (Excel) that provides project summary information. Briefly stated, each project manager completes her/his own project summary (another interesting little tool) containing information on one or more projects - usually more - and submits it prior to the meeting. The report is in three sections over two worksheets .Section one provides financials and dates, and section two provides commentary on status, issues, or mitigation plans. Section 3 is on a separate worksheet, and includes accomplishments and next steps. In each section, a project code is in place to identify the details. The submissions have to be initially rolled up by department, and then at the executive level.Copying and pasting was just not going to be "elegant" in this case.
Process
All submissions are stored in a common folder prior to processing. When the folder is selected for processing, things get underway by first deleting the database (using the FileSystemObject FileExists and DeleteFile methods). Next the database is created within the MS Access workspace using the CreateDatabase method.Using the new database object, three tables are created to receive project data. Actually, the tables get a little more than just the project data.
When there is a possibility of lengthy text data in a cell, the original RowHeight property value is captured to ensure that the final result will present properly. Since this report can be fairly lengthy, hyperlinks are dynamically created to allow for ease in navigation while reviewing the report onscreen. The hyperlink address is also a column in one of the tables, so that at report time all required information is available.
Once all input has been captured, the reporting phase is initiated. This is a separate menu item to allow for report recreation without recapturing data.
The final report also has three sections. The first contains financials and dates for all projects (as many as 125 have been reported, one row for each). The second contains commentary on issues, etc.. only for those projects originally providing these data. Here's where the hyperlinks really make reviewing easy, as well as the rowheight to ensure that all information is presented.
The third section is on a separate worksheet and again only holds data for those projects providing these data. Hyperlinks are also in use here as is the rowheight.
This process was never attempted manually. It was developed because of what can be done through automation. I was the original user - and still use it bi-weekly and we have provided this tool to some other departments as well. The dynamic database creation and ease of use make this tool very portable.
The Bottom Line
For the MS Access database functionality, a reference to the Microsoft DAO 3.6 object model is required. The database and table creation code is pretty straightforward in this case because of its simple and temporary nature, but there's a lot more that can be done such as setting up table indexes and modifying table column attributes.The bottom line is that dynamic MS Access usage can really open up processing possibilities in a wide variety situations that may be somewhat arduous if only the native application object model is considered for data manipulation and reporting.
Dim wrkDefault As Workspace </font> strPath = GetPathName(Application.VBE.VBProjects("PSCT").Filename) End Function
Dim dbsNew As Database
Dim tblDef As TableDef
Dim fldDef As Field
Dim strPath As String
Dim strFile As String
Dim fso As FileSystemObject
Set wrkDefault = DBEngine.Workspaces(0)
RefreshDB = ""
If ActiveWindow Is Nothing Then
Workbooks.Add
End If
strFile = strPath & "\" & MDBNAME
Set fso = New FileSystemObject
If fso.FileExists(strFile) Then
fso.DeleteFile (strFile)
End If
Set fso = Nothing
Set dbsNew = wrkDefault.CreateDatabase( _
strFile, _
dbLangGeneral)
With dbsNew
Set tblDef = .CreateTableDef("ProjectSummary")
With tblDef
.Fields.Append .CreateField("psProject", dbText, 255)
.Fields.Append .CreateField("psDept", dbText, 64)
.Fields.Append .CreateField("psPM", dbText, 64)
.Fields.Append .CreateField("psPhase", dbText, 64)
.Fields.Append .CreateField("psType", dbText, 32)
.Fields.Append .CreateField("psApp", dbText, 255)
.Fields.Append .CreateField("psDateStart", dbText, 10)
.Fields.Append .CreateField("psDateEnd", dbText, 10)
.Fields.Append .CreateField("psValue", dbDouble)
.Fields.Append .CreateField("psActuals", dbDouble)
.Fields.Append .CreateField("psPctComplete", dbInteger)
End With
.TableDefs.Append tblDef
Set tblDef = .CreateTableDef("Indicators")
With tblDef
.Fields.Append .CreateField("iProject", dbText, 255)
.Fields.Append .CreateField("iSeq", dbInteger)
.Fields.Append .CreateField("iID", dbText, 64)
.Fields.Append .CreateField("iStatus", dbText, 1)
.Fields.Append .CreateField("iRowHeight", dbSingle)
.Fields.Append .CreateField("iRefRow", dbInteger)
.Fields.Append .CreateField("iHyperLinkAddress", dbText, 6)
.Fields.Append .CreateField("iReason", dbMemo)
End With
.TableDefs.Append tblDef
Set tblDef = .CreateTableDef("Accomplishments")
With tblDef
.Fields.Append .CreateField("aEngagement", dbText, 255)
.Fields.Append .CreateField("aRowHeight", dbSingle)
.Fields.Append .CreateField("aDesc", dbMemo)
.Fields.Append .CreateField("aComments", dbMemo)
End With
.TableDefs.Append tblDef
End With
dbsNew.Close
RefreshDB = strPath & "\" & MDBNAME
Posted in Office (All) | 2 Comments »