لطفا منتظر باشید ...
Practical 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 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.
Listing 13-18. Initializing the Connection Object
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.
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
Listing 13-19. Destroying the Connection Object
Public Sub DestroyConnection()
Set mcnConnection = Nothing
End Sub
Modifying the Application to Load Data-Validation Lists from the Database
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.
Listing 13-20. Loading the Application Data
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.
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
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 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.
Figure 13-19. Time-Entry Data-Conversion Section
[View full size image]
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.Listing 13-21. The BILLABLE_HOUR Type Structure
The data access tier procedure that consumes this type structure and inserts its data into the database is shown in Listing 13-22.
Public Type BILLABLE_HOUR
lConsultantID As Long
dteDateWorked As Date
lProjectID As Long
lActivityID As Long
dHours As Double
End Type
Listing 13-22. The bInsertTimeEntry 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 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
Listing 13-23. The PostTimeEntriesToDatabase Procedure
Chapter 13
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
PETRAS Reporting
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
