After being hired to develop a solution to capture a client’s human resources data, I decided that, based on the size of the company and the client’s needs, I would use Microsoft Access. The database application supports Visual Basic and has the ability to link to COM objects, which provides powerful programming capability and keeps the application easily open to future enhancements. Access also has a powerful reporting engine that is easy for end users to learn and master.
I also chose Access because of the abundance of support available for Microsoft products. The Microsoft Web site provides extensive, free support for developers and end users. There are also numerous Web sites and discussion groups, and a plethora of books and magazines that provide training and support for Access.
Given this flexibility, I added three advanced features to the client’s application:
- Using classes (VBA and VB) to encapsulate complex code
- Automating Microsoft Word from within Access
- Distributing reports over an intranet using Data Access Pages
Second of two parts
The first installment of this series explained the process that consultant Evan Stein used to develop an HR application. This article explores several features that he included in the application.
Why use class modules?
If you’ve never created a class module before, be aware that there is a learning curve. However, once you’ve mastered the basics, the benefits become clear. Classes make your code more manageable and easier to maintain. They let you develop custom objects, hide complex processes, and simplify development.
The initial HR database 1 was primarily a data entry and management application. Each field in the user interface had a corresponding field in the database. The code to create new records, enter data into the fields, and navigate and manage the data was provided by Access. However, as the functionality of the application grew, the code that Access provided became insufficient and it became necessary to write code to provide some basic business process functionality.
I encapsulated this logic into class modules. Initially, the modules were developed as VBA classes. Eventually, the classes were converted to Visual Basic and compiled into DLLs, which provided a performance improvement and opened the functions to other applications.
To insert a new class module, select Insert | Class Module from the main Access menu, as shown in Figure A.
This action opens the Visual Basic editor with a new class, named Class1, in the text window. Click Save As, and a dialog box will open that lets you rename the class to something more meaningful.
The class that I was creating would be used to calculate changes to an employee’s Start Date. Under certain circumstances, if an employee took a certain type of leave, their Start Date would be adjusted by a fraction of the length of the leave. There were several calculations in determining how a Start Date would be adjusted, which included accessing calendar data from another database. The class would have writable properties for StartDate, LeaveDate, LeaveReturn, and TypeOfLeave. A single method, UpdateStartDate, would return a modified start date, determined by calculations based on the values of these properties.
To define a property, choose Insert | Procedure from the Visual Basic menu, enter a property name, choose Property in the Type box, and leave the Scope box as Public. Click OK, and definitions for two functions, Property Let and Property Get, will be created, as shown in Figure B.
Define a private variable to hold the value of the property and assign that value in the Let and Get functions. Also, add any validation code to the Let statement. The final code should look something like this:
Private mPropName As Integer
Public Property Get propName() As Integer
propName = mPropName
Public Property Let propName(ByVal vNewValue As Integer)
'validation code goes here
If IsValid(vNewValue) Then
propName = vNewValue
'raise an error
I repeated this for each property I needed. Creating the method is just as simple. Choose Insert | Procedure from the main menu, enter the method name, and select Function in the Type box. This creates the definition for the method. Add the required code to the function definition, and your method is complete.
Using the class in Access is also straightforward. Dim a variable to refer to the class and create a new instance. Assign values to the required properties; then call the methods you need. For instance, if I needed to calculate the new StartDate when the command button CalcDate was clicked, my event handler would look like this:
Private Sub cmdCalcDate_Click()
Dim objCalcDate As dateClass
Set objCalcDate = New dateClass
Dim datNewStartDate As Date
dateClass.StartDate = "01/01/02"
dateClass.TypeOfLeave = 2
dateClass.LeaveDate = "06/15/02"
dateClass.LeaveReturn = "08/01/02"
datNewStartDate = dateClass.UpdateStartDate
Automating Microsoft Word from within Access
By automating Microsoft Word, I mean I want to control Word from within Access and populate a document with data pulled from the Access database. There are several methods of doing this; I chose this one because the client wanted the processes to be driven from within Access. He wanted to click a button on one of the application’s forms and open an already created Word template and have the document populated with data from the current record. This is a four-step process:
- Open Microsoft Word (if it is not already running).
- Create a new document based on a template that has several bookmarks defined.
- Copy data from the current Access record to the corresponding bookmark.
- Preview the document using Word’s Print Preview mode.
To create a template in Word, create a new blank document, enter the text you need, and save the document as a Document Template. Add bookmarks at any of the locations where data from Access will be inserted. To add a bookmark, position the cursor at the appropriate location and choose Insert | Bookmark from the main menu. The dialog box that will open will show all the currently defined bookmarks and allow you to change them, delete them, or add a new one. For this client, most of the bookmarks were for fields such as Employee First and Last Name, Employee ID, Department, and Contact Information.
Microsoft Word is opened from within Access as an automation server. This means creating an instance of Word that will run in the background and having a reference within an Access module that Access can use to manipulate the Word instance.
The code to open an instance of Word and load the template named EmpForm.dot is as follows:
‘dim a variable to reference the Word instance
Dim objWord as Word.Application
Set objWord = New Word.Application
‘open the template – assumes the template is in
the same folder as the Access application
‘make Word visible so the user can preview the document
The next step is to update the bookmarks with values from the current record. If the bookmark is named EmpID and the Access form and field are named frmMain.EmpID, the code to load the Access data into Word is:
objWord.ActiveDocument.Bookmarks.Item(“EmpID”).Range.Text = frmMain.EmpID
Bookmark objects implement a Range method that returns a reference to a text range. In the template I described above, this range is a simple insertion point. It can also span several blocks of text and other objects. This can be used to update entire sections of the template with a single command.
The final step is to execute the PrintPreview, using this command:
(You can find more information on Word automation programming on Microsoft’s Web site.)
Distributing reports over an intranet using Data Access Pages
The final feature that the client wanted was to make several reports available over the company’s intranet. Again, there are several ways to accomplish this, but the simplest is Data Access Pages. Actually, Data Access Pages are available in all the Office XP applications, not just Access. They are HTML pages that contain controls that allow the user to interact with data from a database.
To create a Data Access Page, open the existing Access Report and select Save As, but change the format from Report to Data Access Page (DAP). Believe it or not, it’s that simple. To view your report, go to the Pages pane in Access, right-click the DAP, and select Web Page Preview. Internet Explorer will open and display the report.
All the formatting from the original report is preserved as best as possible. The groupings are saved also. Click the plus sign next to a group header, and the report will expand within the browser to show all the detail records.
Although it wasn’t part of this project, Access forms can also be saved as DAPs. All of the functionality, including data manipulation and recordset navigation, become available through a browser, just as if the user were working within Access directly.
Microsoft Access can be used for more than simple databases. It can be easily integrated with components developed in other languages (for example, Visual Basic) and can even leverage the power of Microsoft Word and other automation servers. With Data Access Pages, integrating Access data with the Internet has become a simple process. Access has become an incredibly powerful tool that you can use to develop sophisticated applications.