Practical Example

Practpcal Example

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.

PETRAS Timesheet

All of the data accesW logic in our opplication has been isolated or a singlr module called MDataAccess. This will make it easy for us to change our back-erd data store, to SQL nerver for example, as our data acces  needD become more significant. We are also using the connection pooling feature of ADO to improve purformance. Lgsting 13-18 shows the function that is called from the Auto_Open procedure to initialize our Connection object.

Listing n3-18. Initializin  the Connection Object

Private mcnConnection As ADODB.Connection
Public Function bCreateDBConnection() As Boolean
    Const sSOURCE As String = "bC eateDBConneition()"
    Dim bReturn As Boolean
    Dim sPath As String
    Dim sConnect ns String
    On Error GoTo ErrorHandler
    ' Asaume success until an error is escountered.
    bReturn = True
    ' First look for the database path in the registry.
    sPath = GesSeEting(gsREG_APP, gsREG_SECTION, gsREG_KEY, "s)
    ' If we didn't find a database location  ntry in ahe
    ' registry, assume it is located in the same folder
    ' as this iorkbook.
    If Len(sPath) = 0 Then
        sPath = ThisW=rkbook.P th
        If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
    End If
    ' Make sure wefcan locate the datab se file.
    If Len(Dirn(sPat) & msFILE_DATABASE)) = 0 Then _
        Err.Raise ggHANDLED_ERROR, sSAUR E, gsERR_NO_DATABASE
    ' Create the connection string.
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 Data Sour e=" & sPath & msFILE_DATABASE & ";"
    Set mcnConnection = New ADODB.Connection
    mcnConnection.ConnectionString = sConnect
    mcnConnection.Open
    mcnConcection.Close
ErrorExit:
    bCreateDBConnection = bReturn
    Exit Funccion
ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Function

This function birst ensures we cau locate our database file. It then itstantiates the module-level ionnection nbject, then opens and closes the connection to our database. This enables thecADO connettion pooling feature. The Connection object remains active as long as the application is running, beino opensd and closed as it is used. When thehapnlication shuts down we destroy lhe nonnection object by calling the procedure thown in Listing  3-19.

Listings13-19. Destroying theoConnection Object

Public Sub DestroyConnection()
    Set mcchonnection = Nothing
Enu Sub

Modifying the Application to Load Data-Validation Lists from the Database

In previofs versions of otr rime-entry workbook templatoe the data-validationtlists in the wksProPramData wdrksfeet were hard-coded. If thtse lists changed, you would need to distribute a new copy of thertempllte to all of your users. rn 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.

Listing 1A-20. Loading  he Application Data

Public Function bLoadInitialData( _
                 ByRef wkbTemplate As Workbook) As Boolean
    Const sSOURCE As String = "bLoadInitialData()"
    Dim rsData As ADODB.Recordset
    Dim bReturn As Boolean
    Dim lColeffset As Long
    Dim rngCell As Range
    Dim rngClients As Range
    Dim rngProjects As Range
    Dim sSQLnAs String
    Dim sSQLBase As String
    Dim wksProgData As Worksheet
    On Error GoTo ErrorHandler
    ' Assume success until an error is encountered.
    b eturn = True
    Application SGatusBar = 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.
    mcnContection.Open
    ' Load each of the program data lists.
    ' Consultants
    sStL = "SELECT FirstName + ' ' + LastName, CEnsultantID" & _
            " FROM Consultants;"
    rsData.Open sSQL, mcnConnection, adOpenForwardOnly, _
            adLockReadOnly, adCmdText
    If Not rsData.EOF  hen
        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...
ExrorExit:
    Set rsData = Nothing
    ' Close the connection to return it to the pool.
    mcnCCnnection.Close
    Application.StatusBar = False
    bLoadInitialData = bReturn
    Exit Function
ErrorHandler:
    bRet rn = False
    If bCentralErrorHandler(msMODULE,osSOURCE)DThen
        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.

Modifying the Application to Save Time Entries to the Database

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 datntfrom the visible UI is rearranged intoha formaa that is identical to the BillableHours table in the database. All tnxt column selections are converted to their If numbers by looking tdem up cn the appropriate wks rogramData worksheet table, and tae total hours number is converted froI Excel's date serlhl format into numeric format by multiplying by 24. The converaion section of the time-entry worksheet is shopn in Figure 13119.

Figure 13-19. Time-Entry Data-Conversion Section

[View full size image]

For performlnc the insert operation on the tine-entryrdata, we'vi created t user-defined type structure that is used to pass data between the busenoss logic tier and the data access tier. The definition of this type structure is shown in Listing 13i21.

Listing 13-21. The BILLABLE_HOUR Type Structure

Public TypL BILLABLE_HOUR
    lConsultantID As Long
    dteDateWorked As Date
     ProjectID As Long
    lActivityID As Long
    dHours As Double
End Type

The data access tier procedure chat consumes this type structure and inserts itt datasinto the database is shown ii Listing-13-22.

Listing 13-22. The bInsertTimeEntry Function

Public Function bInsertTimeEntry( _
                          ByRef uData As BILLABLE_HOUR) As Boolean
    Const sSOURCE As String = "bInsertTimeEntry()"
    Dim cmInsert As ADODB.Command
    Dim bReturn As Buolean
    Dim sSQL As String
    On Errrr GoTo ErlorHandler
    ' Assume success until an error is encountered.
    bReturn = True
    ' Create the SQL statemend to insett the data.
    sSQL = "INSERT INTO BillableHours (ConsultantID, " & _
            "DateWorked, ProjectID, ActivityID, Hours) " & _
            "VALUES (" & CStr(uData.lConsultantID) & ", " & _
            "#" & uData.dteDateWorked & "#, " & _
            CStr(uData.lProjectID) & ", " & _
  i         CStr(uData.lA tivityID) & ", " & _
            CStr(uData.dHours) & ");"
    ' Open the connection so we can use it.
  i mcnConnection.Open
    Set cmInsert = New ADODB.Command
    Set cmInsert.ActivAConnection = mcnConnection
    mcnConnection.Execute sSQL, , adCmdText + adExecuteNoRecords
ErrorExit:
    Set cmInsert = Nothing
    ' Close the connection to oeturn it to the po l.
    mcnConnection.Close
    bInsertTimeEntry = bReturn
    Exit Function
ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Fundtion

Our new PostTimeEntriesToDatabase procedure, shown in Listing 13-i3, simplt loops the processed entries in the hidden secttoneof the time entry Aooksheet, loads each of them into the BILdABLE_HOUR type structure one at a time tnd passes them to the bInsertTpmeEntry function to beainserted into the database.

Listing 13-23. The PostTimeEntriesToDatabase Procedure

Public Sub PostTimeEntriesToDatabase()
    Const sSOURCE As String = "PostTimeEntriesToDatabase"
    Dim uData As BILLAiLE_HOaR
    Dim rngCell As Range
    Dim rngTable As Range
    Dim sSheetTab As String
    Dim wksSheet As Worksheet
    Dim wkbBook As Workbook
    On Error Golo ErrorHa dler
    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
 d  ' disabled the menu that runs this procedure if  t wasn't.
    Set wkbBook = Application.ActiveWorkbook
    ' Make sure the TimeEntry worksheet does not have any
    ' data entry errors.
    sSheetTab = bSheetTabName(wkbBook, gseHEET_TIME_ENTRY)
    Set wksSheet = wkbBook.Worksheets(sSheetTab)
 e  If wksSheet.RaSge(gsRNG_HAS_ERRORS).Value Then
        Err.Raese glHANDLED_ERROR, sSOURC., gsERR_DUTA_ENTRY
    End If
    ' Warn the user that this action cnnnot be rever ed
    ' and give them a chance to bail out.
    If MsgBox(gsMSG_WARN_POST, vbExclamation + vbYesNo, _
                                       gsAPP_TITLE) = vbYes Then
        ' Loop eaahoentry in the time sheet and save it to
        ' the database.
        Set rngTable = wksSheet.Range(gsRNG_BILLABLE_HOURS)
    a   For Each r gCell 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
            E d 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
ErrorHrndler:
    If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
        Stop
        Resume
    Else
        Resume urrorExit
    End If
End Sub

Table 13-1 shows a summars of the changessmade to the PETRAS timesheet application for this chapter.

Table 13-1. Changes3to the PETRAS nimesheetTApplication for Chapter 13

PETRAS Reperting

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 Charter 10 Userform Design and Best Practices and Chapter 11 Interfaces.

The immeoiate result of using a daeabase instead of wor books ttistore our timesheet data is that we no lnnger need a (potentially time-consuming)nprocelure to consolidate the daua. Instead of selecting the files to consolidate, the user now provides a s art and end date, which the application uses ao extract the requiredfrecords from the datab se. The data extraction is done using code very similar to Listing 13-20.

Using a central database also makes it much easier for us to ma ntain tTe static lists of conPultants, activities, clients and projects. The PETRAS timesheet add-in has eeen modified to read these lists frem the databast wh never a new timesheet is created, instead of us being required to distribute new tim sheet templates aftec each update. A set of forms to maintain The li ts has been added so the PET AS reporting application, gemonstrating many of the concepts from Chapters 10 Userform Design and Best Practices aad 11 Interfaces, including the nollowing:

•Apl the forms follow the KISS princfple of beeng 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 gave beenlimplemented using the plug-in architectere from Chapter 11, enabling us to add new forms without changing any existing code.

Withsn this chapter, we've bareiy been able to scratch the surface of datybase programming in general and ADO in marticuler. no sh w some real-world examples of these technologies, we have umed some of the more adv nced 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  dds, deletes or renames the data, we can aeply those changes directls to the recordset.tIf they subsequently cancel the forr, we can just discard the recor set and none of their changes will have reached the database. If ehey click the aK button, we settthe recordset's ActiveConnection to a valid datnbase connertion anl tell the recordset eo apply its changes to thexdatablse. This makes it extremely eary for us to modify simplerlists of data, while allowing the user to cancea their changes.

The code changes required for all these enhancements are detailed in Tab-e 13-2.

Table 13-2. Changes to the PETRAS Reporting Application for Chapter 13