In this section we will move our data into an Access database. We''ve provided a ready-made Access database for this project. Creating an Access database is easy and after you''ve created the database you no longer need Access to use it.
All of the data access logic in our application has been isolated in a single module called MDataAccess. This will make it easy for us to change our back-end data store, to SQL Server for example, as our data access needs become more significant. We are also using the connection pooling feature of ADO to improve performance. Listing 13-18 shows the function that is called from the Auto_Open procedure to initialize our Connection object.
Private mcnConnection As ADODB.Connection Public Function bCreateDBConnection() As Boolean Const sSOURCE As String = "bCreateDBConnection()" Dim bReturn As Boolean Dim sPath As String Dim sConnect As String On Error GoTo ErrorHandler '' Assume success until an error is encountered. bReturn = True '' First look for the database path in the registry. sPath = GetSetting(gsREG_APP, gsREG_SECTION, gsREG_KEY, ") '' If we didn''t find a database location entry in the '' registry, assume it is located in the same folder '' as this workbook. If Len(sPath) = 0 Then sPath = ThisWorkbook.Path If Right$(sPath, 1) <> "\" Then sPath = sPath & "\" End If '' Make sure we can locate the database file. If Len(Dir$(sPath & msFILE_DATABASE)) = 0 Then _ Err.Raise glHANDLED_ERROR, sSOURCE, gsERR_NO_DATABASE '' Create the connection string. sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & sPath & msFILE_DATABASE & ";" Set mcnConnection = New ADODB.Connection mcnConnection.ConnectionString = sConnect mcnConnection.Open mcnConnection.Close ErrorExit: bCreateDBConnection = bReturn Exit Function ErrorHandler: bReturn = False If bCentralErrorHandler(msMODULE, sSOURCE) Then Stop Resume Else Resume ErrorExit End If End Function
This function first ensures we can locate our database file. It then instantiates the module-level Connection object, then opens and closes the connection to our database. This enables the ADO connection pooling feature. The Connection object remains active as long as the application is running, being opened and closed as it is used. When the application shuts down we destroy the connection object by calling the procedure shown in Listing 13-19.
Public Sub DestroyConnection() Set mcnConnection = Nothing End Sub
In previous versions of our time-entry workbook template, the data-validation lists in the wksProgramData worksheet were hard-coded. If these lists changed, you would need to distribute a new copy of the template to all of your users. In this section we will modify the application so it automatically loads the latest versions of these lists from our new database whenever a time-entry workbook is opened.
The procedure that accomplishes this task is located in our MDataAccess module and called by our application event handler whenever a time-entry workbook is created, opened or detected on application startup. The entire procedure is long and very repetitive, so we will only show a representative sample of it in Listing 13-20.
Public Function bLoadInitialData( _ ByRef wkbTemplate As Workbook) As Boolean Const sSOURCE As String = "bLoadInitialData()" Dim rsData As ADODB.Recordset Dim bReturn As Boolean Dim lColOffset As Long Dim rngCell As Range Dim rngClients As Range Dim rngProjects As Range Dim sSQL As String Dim sSQLBase As String Dim wksProgData As Worksheet On Error GoTo ErrorHandler '' Assume success until an error is encountered. bReturn = True Application.StatusBar = gsSTATUS_LOADING_DATA '' Clear any existing data from the wksProgramData worksheet. Set wksProgData = wkbTemplate.Worksheets(gsSHEET_PROG_DATA) wksProgData.UsedRange.Offset(1, 0).ClearContents '' Create the Recordset object we''ll use for all the queries. Set rsData = New ADODB.Recordset '' Get a connection from the pool. mcnConnection.Open '' Load each of the program data lists. '' Consultants sSQL = "SELECT FirstName + '' '' + LastName, ConsultantID" & _ " FROM Consultants;" rsData.Open sSQL, mcnConnection, adOpenForwardOnly, _ adLockReadOnly, adCmdText If Not rsData.EOF Then wksProgData.Range(gsRNG_CONSULT_TOP).Offset(1, 0) _ .CopyFromRecordset rsData Else Err.Raise glHANDLED_ERROR, sSOURCE, _ "Error retrieving consultant data." End If rsData.Close '' Load the rest of the lists here... ErrorExit: Set rsData = Nothing '' Close the connection to return it to the pool. mcnConnection.Close Application.StatusBar = False bLoadInitialData = bReturn Exit Function ErrorHandler: bReturn = False If bCentralErrorHandler(msMODULE, sSOURCE) Then Stop Resume Else Resume ErrorExit End If End Function
The select operation using the recordset should look very familiar. It is essentially identical to the operation we demonstrated in the Retrieving Data section above. Notice how we open our module-level Connection object at the beginning of the procedure and close it at the end. This is the proper way to make use of a pooled connection.
In previous versions of our application, the entire completed time-entry workbook was saved to a central consolidation location. In this section we modify the application to save just the billable hours data to our new database. We''ve added a new hidden section to our time-entry worksheet that converts the data entered by the user into a format that can be loaded into the database.
The data from the visible UI is rearranged into a format that is identical to the BillableHours table in the database. All text column selections are converted to their ID numbers by looking them up in the appropriate wksProgramData worksheet table, and the total hours number is converted from Excel''s date serial format into numeric format by multiplying by 24. The conversion section of the time-entry worksheet is shown in Figure 13-19.
For performing the insert operation on the time-entry data, we''ve created a user-defined type structure that is used to pass data between the business logic tier and the data access tier. The definition of this type structure is shown in Listing 13-21.
Public Type BILLABLE_HOUR lConsultantID As Long dteDateWorked As Date lProjectID As Long lActivityID As Long dHours As Double End Type
The data access tier procedure that consumes this type structure and inserts its data into the database is shown in Listing 13-22.
![[View full size image]](/image/library/english/13850_13fig19_alt.jpg;380140){kind=link}
Public Function bInsertTimeEntry( _
ByRef uData As BILLABLE_HOUR) As Boolean
Const sSOURCE As String = "bInsertTimeEntry()"
Dim cmInsert As ADODB.Command
Dim bReturn As Boolean
Dim sSQL As String
On Error GoTo ErrorHandler
'' Assume success until an error is encountered.
bReturn = True
'' Create the SQL statement to insert the data.
sSQL = "INSERT INTO BillableHours (ConsultantID, " & _
"DateWorked, ProjectID, ActivityID, Hours) " & _
"VALUES (" & CStr(uData.lConsultantID) & ", " & _
"#" & uData.dteDateWorked & "#, " & _
CStr(uData.lProjectID) & ", " & _
CStr(uData.lActivityID) & ", " & _
CStr(uData.dHours) & ");"
'' Open the connection so we can use it.
mcnConnection.Open
Set cmInsert = New ADODB.Command
Set cmInsert.ActiveConnection = mcnConnection
mcnConnection.Execute sSQL, , adCmdText + adExecuteNoRecords
ErrorExit:
Set cmInsert = Nothing
'' Close the connection to return it to the pool.
mcnConnection.Close
bInsertTimeEntry = bReturn
Exit Function
ErrorHandler:
bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
Our new PostTimeEntriesToDatabase procedure, shown in Listing 13-23, simply loops the processed entries in the hidden section of the time entry worksheet, loads each of them into the BILLABLE_HOUR type structure one at a time and passes them to the bInsertTimeEntry function to be inserted into the database.
Public Sub PostTimeEntriesToDatabase() Const sSOURCE As String = "PostTimeEntriesToDatabase" Dim uData As BILLABLE_HOUR Dim rngCell As Range Dim rngTable As Range Dim sSheetTab As String Dim wksSheet As Worksheet Dim wkbBook As Workbook On Error GoTo ErrorHandler If Not bInitGlobals() Then Err.Raise glHANDLED_ERROR '' We know the active workbook is a time-entry workbook '' because our application event handling class would have '' disabled the menu that runs this procedure if it wasn''t. Set wkbBook = Application.ActiveWorkbook '' Make sure the TimeEntry worksheet does not have any '' data entry errors. sSheetTab = sSheetTabName(wkbBook, gsSHEET_TIME_ENTRY) Set wksSheet = wkbBook.Worksheets(sSheetTab) If wksSheet.Range(gsRNG_HAS_ERRORS).Value Then Err.Raise glHANDLED_ERROR, sSOURCE, gsERR_DATA_ENTRY End If '' Warn the user that this action cannot be reversed '' and give them a chance to bail out. If MsgBox(gsMSG_WARN_POST, vbExclamation + vbYesNo, _ gsAPP_TITLE) = vbYes Then '' Loop each entry in the time sheet and save it to '' the database. Set rngTable = wksSheet.Range(gsRNG_BILLABLE_HOURS) For Each rngCell In rngTable uData.lConsultantID = rngCell.Value uData.dteDateWorked = rngCell.Offset(0, 1).Value uData.lProjectID = rngCell.Offset(0, 2).Value uData.lActivityID = rngCell.Offset(0, 3).Value uData.dHours = rngCell.Offset(0, 4).Value If Not bInsertTimeEntry(uData) Then Err.Raise glHANDLED_ERROR End If Next rngCell '' Clear the time entry worksheet and display a success '' message to the user. wksSheet.Range(gsRNG_CLEAR_INPUTS).ClearContents MsgBox gsMSG_POST_SUCCESS, vbInformation, gsAPP_TITLE End If ErrorExit: Exit Sub ErrorHandler: If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then Stop Resume Else Resume ErrorExit End If End Sub
Module
Procedure
Change
CAppEventHandler
bInitializeWorkbook
Centralized workbook initialization code here
bLoadInitialData
Call this procedure to load initial timesheet data
MDataAccess
New module to handle all the database connectivity
MEntryPoints
PostTimeEntriesToNetwork
Converted to PostTimeEntriesToDatabase
SpecifyConsolidationFolder
Converted to SpecifyDatabaseLocation
Auto_Open
bCreateDBConnection
Call this procedure to create pooled Connection object
ShutdownApplication
Destroy pooled Connection object on close
The PETRAS reporting application has had a number of changes and additions, partly to demonstrate the database handling concepts introduced in this chapter, but also to demonstrate some of the more interesting concepts introduced in Chapter 10 Userform Design and Best Practices and Chapter 11 Interfaces.
The immediate result of using a database instead of workbooks to store our timesheet data is that we no longer need a (potentially time-consuming) procedure to consolidate the data. Instead of selecting the files to consolidate, the user now provides a start and end date, which the application uses to extract the required records from the database. The data extraction is done using code very similar to Chapters 10 Userform Design and Best Practices and 11 Interfaces, including the following:
All the forms follow the KISS principle of being simple for the user.
All the forms have their code separated between a user interface layer (the form''s module) and a separate user interface support (UIS) layer, implemented as a class module specific to each form.
All the forms are resizable, implemented using the CFormResizer class from Chapter 10.
The maintenance of the client and project lists has been implemented using a TreeView control to show the client/project hierarchy.
All the forms have been implemented using the plug-in architecture from Chapter 11, enabling us to add new forms without changing any existing code.
Within this chapter, we''ve barely been able to scratch the surface of database programming in general and ADO in particular. To show some real-world examples of these technologies, we have used some of the more advanced techniques covered in the Further Reading texts. Specifically, we have used disconnected recordsets to handle the underlying data for our userforms. With these recordsets, we create a connection to the database, populate the recordset, then set the recordset''s ActiveConnection property to Nothing. That disconnects the recordset from the physical database file, allowing us to change the data contained in the recordset without the database being updated. So when our user adds, deletes or renames the data, we can apply those changes directly to the recordset. If they subsequently cancel the form, we can just discard the recordset and none of their changes will have reached the database. If they click the OK button, we set the recordset''s ActiveConnection to a valid database connection and tell the recordset to apply its changes to the database. This makes it extremely easy for us to modify simple lists of data, while allowing the user to cancel their changes.Chapter 13
Module
Procedure
Change
General changes for database handling
MDataAccess
New module to handle all the database connectivity.
MEntryPoints
MenuSpecifyDatabaseLocation
New procedure for the user to select the location of the central PETRAS database file.
MBrowseForFolder
New module to show the standard Browse for Folder dialog.
Extracting data instead of consolidating workbooks
MSystemCode
ImportData
Renamed from ConsolidateWorkbooks. Extracts data from the database instead of looping through workbooks.
FImportData
New userform to provide a range of dates for extracting timesheet records.
MDataAccess
GetTimesheetData
Retrieve the timesheet records for the given date range, writing the records to the results workbook.
FProgressBar, CProgressBar, IProgressBar
The three progress bar modules have been removed, because they are no longer required to show the progress of the consolidation process.
Userforms to maintain the static lists
wksCommandBars
New menu structure created for the database interaction.
MEntryPoints
Added procedures called by the new menu items, one for each new form.
FActivities
New form to maintain the list of Activities. The code in the form concentrates on handling the user interaction.
CUISActivities
New class to support the FActivities form. The code in the class concentrates on managing the disconnected recordset, in response to the user actions.
FConsultants
New form to maintain the list of Consultants.
CUISConsultants
New class to support the FConsultants form.
FClients
New form to maintain the lists of Clients and Projects.
CUISClients
New class to support the FClients form.
MDataAccess
New procedures to create the disconnected recordsets for the Activities, Consultants and Clients/Projects forms and to update the database with the changes to the recordsets.
CFormResizer
New class to handle the resizing of the new forms.
Implementing the plug-in userform architecture
IPlugInForm
New class to define the IPlugInForm interface.
FActivities, FConsultants, FClients
The three new data-maintenance forms implement the IPlugInForm interface.
MSystemCode
ShowForm
A generic procedure to show any of the plug-in forms.