
The Data control provides a means of quickly developing database applications with little or no code, but it limits your access to the underlying database. The Microsoft Jet database engine exposes another method of working with data-bases: Data Access Objects (DAO). Although using DAO requires more coding than using the Data control, it offers complete programmatic access to every-thing in the database, as well as significantly greater flexibility. This chapter shows you how to use Jet Data Access Objects to perform common database operations.
All the examples in this chapter use Microsoft Access (.MDB) database files. The Jet engine can also access other PC-based databases such as dBASE, FoxPro, Paradox, and Btrieve, as well as Open Database Connectivity (ODBC) data sources. The techniques shown can be used with any database that Visual Basic can access through Jet. Chapter 6, "Connecting to an ODBC Server," and Chapter 7, "SQL Server Databases and Remote Data Objects," show how to access ODBC and SQL Server databases.
Browsing and updating records are two of the most basic database operations. In this How-To, you will use unbound controls and Data Access Objects to browse and update a recordset.
Users make data entry errors, and robust applications anticipate and trap those errors. This How-To shows how to trap and respond to user errors when you're using Data Access Objects.
Users expect to be able to undo changes they make while they are working. In this How-To, using Data Access Objects and unbound controls, you will learn how to enable users to undo changes.
Inserting and deleting records are common database activities. In this How-To, you will learn to use Data Access Objects to add and delete records.
Simple text boxes are not the only user interface tools available when you're using unbound controls. In this How-To, you will build a generic form that can run an ad hoc query, display the results, and enable the user to select a record.
Indexes can substantially speed up access to records. This How-To shows how you can leverage indexes for performance.
If you need to know how many records are in your recordset, this How-To will show you how to get that number.
Although Jet is a robust database engine, many things can go wrong when working with a database. This How-To shows you how to handle Data Access Object errors gracefully.
An interesting capability of the Microsoft Jet engine is used to access Excel worksheets. In this How-To, we view and manipulate an Excel worksheet as if it were an ordinary database file.
PROBLEM
Bound recordsets with Data controls are fine for many purposes, but the Data control
has significant limitations. I can't use indexes with bound controls, and I can't
refer to the Data control's recordset if the Data control's form is not loaded. How
can I browse a recordset without using bound controls?
Technique
Visual Basic and the Microsoft Jet database engine provide a rich set of Data Access Objects that give you complete control over your database and provide capabilities beyond what you can accomplish with bound controls.
If you've worked with other databases, and in particular Structured Query Language (SQL), you might be accustomed to dividing the database programming language into Data Definition Language (DDL) and Data Manipulation Language (DML). Although Jet provides programmatic access to both structure and data, DAO makes no clear distinction between the two. Some objects, such as the Recordset, are used strictly for data manipulation, whereas others, such as the TableDef object, act in both data definition and data manipulation roles. Figure 2.1 shows the DAO hierarchy.
Figure 2.1. The Data Access Object hierarchy.
In most cases, you will be using DAO to manage data. There are four general types of data operations:
DAO provides the recordset object for retrieving records and both the recordset object and the Execute method for inserting, updating, and deleting records.
Before you can do anything useful with DAO, you must open a database. In most cases, this is as simple as using the OpenDatabase method of the default workspace. The following code fragment shows some typical code used to open an Access .MDB file and create a recordset:
` Declare database variable
Dim db As Database
Dim rs As Recordset
` Open a database file and assign it to db
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\MyDB.MDB")
` Create a recordset
Set rs = db.OpenRecordset("MyQuery", dbOpenDynaset, , dbOptimistic)
If you are working with a secured database, you will need to take a few extra steps to provide a valid username and password to the database engine so that a secured workspace object can be created. Refer to Chapter 11, "The Windows Registry and State Information," for more information.
Moving Within an Unbound Recordset
When you use the Data control on a bound form, you rarely have to code in order to move operations. The user clicks on the navigational buttons, and the Data control executes a move internally. Only in the case of a delete do you need to code a move operation (see How-To 1.4).
When you use unbound controls, you must refresh the data displayed on the form with your code. The recordset object provides four methods to facilitate this task: MoveFirst, MovePrevious, MoveNext, and MoveLast.
When you use MovePrevious, you should always check to make sure that the movement has not placed the record pointer before the first record in the recordset. Do this by checking the value of the recordset's BOF property. If BOF is True, you're not on a valid record. The usual solution is to use MoveFirst to position the pointer on the first record. Similarly, when you use MoveNext, you should check to make sure that you're not past the last record in the recordset by checking the EOF property. If EOF is True, use MoveLast to move to a valid record. It's also a good idea to ensure that the recordset has at least one record by making sure that the value of the recordset's RecordCount property is greater than zero.
Updating Records
You can update the values in table-type or dynaset-type recordsets. Updating records in a recordset is a four-step procedure:
Steps
Open and run the project HT201.VBP. The form shown in Figure 2.2 appears. Use the navigation buttons at the bottom of the form to browse through the records in the recordset. You can change the data by typing over the existing values.
Table 2.1. Objects and properties for Form1.
OBJECT Property Setting Form Name Form1 Caption Unbound Browser CommandButton Name cmdMove Caption |< Index 0 CommandButton Name cmdMove Caption < Index 1 CommandButton Name cmdMove Caption > Index 2 CommandButton Name cmdMove Caption >| Index 3 TextBox Name txt Index 0 TextBox Name txt Index 1 TextBox Name txt Index 2 TextBox Name txt Index 3 Label Name Label1 Caption &Title Label Name Label2 Caption &Year Published Label Name Label3 Caption &ISBN Label Name Label4 Caption &Publisher ID
Table 2.2. Menu specifications for Form1.
CAPTION Name Shortcut Key &File mnuFile ----E&xit mnuFileExit &Data mnuData ----&Save Record mnuDataSaveRecord Ctrl+S
Figure 2.2. The Unbound Browser form.
[Data Files] BIBLIO=<path to biblio directory>
Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Note: It's up to the client to save
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Flags
` dirty flag
Private mblnIsDirty As Boolean
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
FirstRecord = 1
LastRecord = 2
NextRecord = 3
PreviousRecord = 4
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
ErrInvalidMoveType = vbObjectError + 1000 + 11
ErrNoRecords = vbObjectError + 1000 + 12
End Enum
ENUMERATIONSEnumerations are a feature in Visual Basic that enables you to define publicly available constants within a class module. If you did any work with class modules in Visual Basic 4.0 or earlier, you might have been frustrated by the need to provide a standard "helper" module for any class that used public constants. This type of workaround has been replaced with public enumerations. Note, however, that you can assign only long integer values in an enumeration. No means exist for making strings or other types of constants public in classes.
Private Sub Class_Initialize() ` open the database and recordset Dim strDBName As String ` Get the database name and open the database. ` BiblioPath is a function in READINI.BAS strDBName = BiblioPath() Set mdb = DBEngine.Workspaces(0).OpenDatabase(strDBName) ` Open the recordset. Set mrs = mdb.OpenRecordset( _ "Titles", dbOpenDynaset, dbSeeChanges, dbOptimistic) ` Raise an error if there is no data If mrs.BOF Then RaiseClassError ErrNoRecords End If ` fetch the first record to the properties GetCurrentRecord End Sub Private Sub Class_Terminate() ` cleanup - note that since a Class_Terminate error ` is fatal to the app, this proc simply traps and ` ignores any shutdown errors ` that's not a great solution, but there's not much ` else that can be done at this point ` in a production app, it might be helpful to log ` these errors ` close and release the recordset object mrs.Close Set mrs = Nothing ` close and release the database object mdb.Close Set mdb = NothingEnd Sub
Private Sub RaiseClassError(lngErrorNumber As CTitlesError)
` Note: DAO errors are passed out as-is
Dim strDescription As String
Dim strSource As String
` assign the description for the error
Select Case lngErrorNumber
Case ErrInvalidMoveType
strDescription = "Invalid move operation."
Case ErrNoRecords
strDescription = _
"There are no records in the Titles table."
Case Else
` If this executes, it's a coding error in
` the class module, but having the case is
` useful for debugging.
strDescription = _
"There is no message for this error."
End Select
` build the Source property for the error
strSource = App.EXEName & ".CTitles"
` raise it
Err.Raise lngErrorNumber, strSource, strDescription
End Sub
Private Sub GetCurrentRecord()
` Get current values from the recordset
` a zero length string is appended to
` each variable to avoid the Invalid use of Null
` error if a field is null
` although current rules don't allow nulls, there
` may be legacy data that doesn't conform to
` existing rules
mstrISBN = mrs![ISBN] & ""
mstrTitle = mrs![Title] & ""
mstrYearPublished = mrs![Year Published] & ""
mstrPubID = mrs![PubID] & ""
End Sub
Private Sub UpdateRecord()
` DAO Edit/Update
On Error GoTo ProcError
` inform DAO we will edit
mrs.Edit
mrs![ISBN] = mstrISBN
mrs![Title] = mstrTitle
mrs![Year Published] = mstrYearPublished
mrs![PubID] = mstrPubID
` commit changes
mrs.Update
` clear dirty flag
mblnIsDirty = False
Exit Sub
ProcError:
` clear the values that were assigned
` and cancel the edit method by
` executing a moveprevious/movenext
mrs.MovePrevious
mrs.MoveNext
` raise the error again
Err.Raise Err.Number, Err.Source, Err.Description, _
Err.HelpFile, Err.HelpContext
End Sub
Public Property Get Title() As String
Title = mstrTitle
End Property
Public Property Let Title(strTitle As String)
mstrTitle = strTitle
` set the dirty flag
mblnIsDirty = True
End Property
Public Property Get YearPublished() As String
YearPublished = mstrYearPublished
End Property
Public Property Let YearPublished(strYearPublished As String)
mstrYearPublished = strYearPublished
` set the dirty flag
mblnIsDirty = True
End Property
Public Property Get ISBN() As String
ISBN = mstrISBN
End Property
Public Property Let ISBN(strISBN As String)
mstrISBN = strISBN
` set the dirty flag
mblnIsDirty = True
End Property
Public Property Get PubID() As String
PubID = mstrPubID
End Property
Public Property Let PubID(strPubID As String)
mstrPubID = strPubID
` set the dirty flag
mblnIsDirty = True
End Property
Public Property Get IsDirty() As Boolean
` pass out the dirty flag
IsDirty = mblnIsDirty
End Property
Public Sub Move(lngMoveType As CTitlesMove)
` Move and refresh properties
Select Case lngMoveType
Case FirstRecord
mrs.MoveFirst
Case LastRecord
mrs.MoveLast
Case NextRecord
mrs.MoveNext
` check for EOF
If mrs.EOF Then
mrs.MoveLast
End If
Case PreviousRecord
mrs.MovePrevious
` check for BOF
If mrs.BOF Then
mrs.MoveFirst
End If
Case Else
` bad parameter, raise an error
RaiseClassError ErrInvalidMoveType
End Select
GetCurrentRecord
End Sub
Declaring the lngMoveType parameter as CTitlesMove instead of as a long integer illustrates another benefit of using enumerations. If a variable is declared as the type of a named enumeration, the code editor provides a drop-down list of available constants wherever the variable is used.
Public Sub SaveRecord()
` save current changes
` test dirty flag
If mblnIsDirty Then
` update it
UpdateRecord
Else
` record is already clean
End If
End Sub
Option Explicit ` CTitles object Private mclsTitles As CTitles ` These constants are used for the various control arrays ` command button constants Const cmdMoveFirst = 0 Const cmdMovePrevious = 1 Const cmdMoveNext = 2 Const cmdMoveLast = 3 ` text box index constants Const txtTitle = 0 Const txtYearPublished = 1 Const txtISBN = 2 Const txtPubID = 3 ` refresh flag Private mblnInRefresh As Boolean
Private Sub Form_Load()
` create the mclsTitles object and display the first record
On Error GoTo ProcError
Dim strDBName As String
Screen.MousePointer = vbHourglass
` create the CTitles object
Set mclsTitles = New CTitles
` fetch and display the current record
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
` An error was generated by Visual Basic or CTitles.
` Display the error message and terminate gracefully.
MsgBox Err.Description, vbExclamation
Unload Me
Resume ProcExit
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` save the current record
mclsTitles.SaveRecord
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
` an error here means the record won't be saved
` let the user decide what to do
Dim strMsg As String
strMsg = "The following error occurred while _
"attempting to save:"
strMsg = strMsg & vbCrLf & Err.Description & vbCrLf
strMsg = strMsg & "If you continue the current operation, " _
strMsg = strMsg & "changes to your data will be lost."
strMsg = strMsg & vbCrLf
strMsg = strMsg & "Do you want to continue anyway?"
If MsgBox(strMsg, _
vbQuestion Or vbYesNo Or vbDefaultButton2) = vbNo Then
Cancel = True
End If
Resume ProcExit
End Sub
Private Sub cmdMove_Click(Index As Integer)
` move to the desired record, saving first
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` save the record
mclsTitles.SaveRecord
` move to the indicated record
Select Case Index
Case cmdMoveFirst
mclsTitles.Move FirstRecord
Case cmdMoveLast
mclsTitles.Move LastRecord
Case cmdMoveNext
mclsTitles.Move NextRecord
Case cmdMovePrevious
mclsTitles.Move PreviousRecord
End Select
` refresh display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub txt_Change(Index As Integer)
` update property values if required
On Error GoTo ProcError
Dim strValue As String
Screen.MousePointer = vbHourglass
` fetch the value from the control
strValue = txt(Index).Text
` check first to see if we're in a GetData call
` assigning the property values while refreshing
` will reset the dirty flag again so the data will
` never appear to have been saved
If Not mblnInRefresh Then
` update the clsTitles properties
Select Case Index
Case txtTitle
mclsTitles.Title = strValue
Case txtYearPublished
mclsTitles.YearPublished = strValue
Case txtISBN
mclsTitles.ISBN = strValue
Case txtPubID
mclsTitles.PubID = strValue
End Select
End If
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuFileExit_Click()
` shut down
` work is handled by the Query_Unload event
Unload Me
End Sub
Private Sub mnuData_Click()
` set enabled/disabled flags for menu commands
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` save enabled only when dirty
mnuDataSaveRecord.Enabled = mclsTitles.IsDirty
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuDataSaveRecord_Click()
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` save it
mclsTitles.SaveRecord
` refresh display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub GetData()
` display the current record
` set the mblnInRefresh flag so that the txt_Change event
` doesn't write the property values again
mblnInRefresh = True
` assign the values to the controls from the properties
txt(txtTitle).Text = mclsTitles.Title
txt(txtYearPublished).Text = mclsTitles.YearPublished
txt(txtISBN).Text = mclsTitles.ISBN
txt(txtPubID).Text = mclsTitles.PubID
` clear the refresh flag
mblnInRefresh = False
End Sub
How It Works
When the form loads, it creates the object variable for the CTitles class and displays the first record. The user can navigate among the records by clicking the move buttons, and the logic in the form and the CTitles class saves changes if the record is dirty.
With placement of the data management logic in the class module and the user interface logic in the form, a level of independence between the database and the user interface is created. Several different forms can be created that all use the same class without duplicating any of the data management logic. Additionally, changes made to the underlying database can be incorporated into the class without requiring changes to the forms based on it.
Comments
The beauty of encapsulating all the data access code in a class module isn't fully revealed until you have an application that enables the user to edit the same data using more than one interface. If, for example, you display a summary of detail records in a grid and also provide a regular form for working with the same data, most of the code for managing that data will be shared in the class modules. Each form that presents the data will only need to have code that controls its own interface.
Problem
I need to verify that data entered is valid before I update the database. How can I do this with Data Access Objects and unbound controls?
Technique
Databases, tables, and fields often have various business rules that apply to the data. You can apply rules by writing code to check the values of the data in unbound controls before you write the changes to the underlying tables.
Class modules are most often used to handle the data-management logic for unbound data--adding a layer of separation between the database schema and the user interface logic. Normally, class modules should not handle any user interaction (unless, of course, the class is specifically designed to encapsulate user interface components), so instead of generating messages, classes raise errors if a validation rule is violated. The error is trapped by the user interface and handled in whatever manner is appropriate.
Steps
Open and run HT202.VBP. Tab to the Year Published text box, delete the year, and attempt to save the record. Because the Year Published is required, an error message is displayed, as shown in Figure 2.3. Experiment with some of the other fields to examine other rules and the messages that are displayed.
Figure 2.3. The Validating Browser form.
Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Flags
` dirty flag
Private mblnIsDirty As Boolean
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
FirstRecord = 1
LastRecord = 2
NextRecord = 3
PreviousRecord = 4
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
ErrMissingTitle = vbObjectError + 1000 + 1
ErrMissingYear = vbObjectError + 1000 + 2
ErrMissingISBN = vbObjectError + 1000 + 3
ErrInvalidYear = vbObjectError + 1000 + 4
ErrMissingPubID = vbObjectError + 1000 + 5
ErrNonNumericPubID = vbObjectError + 1000 + 6
ErrRecordNotFound = vbObjectError + 1000 + 10
ErrInvalidMoveType = vbObjectError + 1000 + 11
ErrNoRecords = vbObjectError + 1000 + 12
End Enum
Private Sub RaiseClassError(lngErrorNumber As CTitlesError)
` Note: DAO errors are passed out as-is
Dim strDescription As String
Dim strSource As String
` assign the description for the error
Select Case lngErrorNumber
Case ErrMissingTitle
strDescription = "The Title is required."
Case ErrMissingYear
strDescription = "The Year Published is required."
Case ErrMissingISBN
strDescription = "The ISBN number is required."
Case ErrInvalidYear
strDescription = "Not a valid year."
Case ErrMissingPubID
strDescription = "The Publisher ID is required."
Case ErrNonNumericPubID
strDescription = "The Publisher ID must be numeric."
Case ErrRecordNotFound
strDescription = "The record was not found."
Case ErrInvalidMoveType
strDescription = "Invalid move operation."
Case ErrNoRecords
strDescription = _
"There are no records in the Titles table."
Case Else
` If this executes, it's a coding error in
` the class module, but having the case is
` useful for debugging.
strDescription = "There is no message for this error."
End Select
` build the Source property for the error
strSource = App.EXEName & ".CTitles"
` raise it
Err.Raise lngErrorNumber, strSource, strDescription
End Sub
Public Property Get IsValid _
(Optional blnRaiseError As Boolean = False) As Boolean
` test the data against our rules
` the optional blnRaiseError flag can be used to have the
` procedure raise an error if a validation rule is
` violated.
Dim lngError As CTitlesError
If mstrISBN = "" Then
lngError = ErrMissingISBN
ElseIf mstrTitle = "" Then
lngError = ErrMissingTitle
ElseIf mstrYearPublished = "" Then
lngError = ErrMissingYear
ElseIf Not IsNumeric(mstrYearPublished) Then
lngError = ErrInvalidYear
ElseIf mstrPubID = "" Then
lngError = ErrMissingPubID
ElseIf Not IsNumeric(mstrPubID) Then
lngError = ErrNonNumericPubID
End If
If lngError <> 0 Then
If blnRaiseError Then
RaiseClassError lngError
Else
IsValid = False
End If
Else
IsValid = True
End If
End Property
Public Sub SaveRecord()
` save current changes
` test dirty flag
If mblnIsDirty Then
` validate, raise an error
` if rules are violated
If IsValid(True) Then
` update it
UpdateRecord
End If
Else
` record is already clean
End If
End Sub
How It Works
Data validation logic is handled entirely within the CTitles class--no special coding is required in the form beyond the normal error handlers that trap errors and display a simple message about the error. The rules applied for validation can be as simple or as complex as the application requires.
Only minor modifications to the class were required to implement data validation. The error enumeration and the corresponding RaiseClassError procedure were expanded to include validation errors, the IsValid procedure was added to perform the validation tests, and a few lines of code were changed in the SaveRecord procedure.
Comments
Not all database rules require you to write code to perform data validation. The Jet database engine can enforce some--or possibly all--of your rules for using the properties of tables and fields or relational constraints. How-To 4.5 shows you how to use these objects and properties to supplement or replace validation code.
Problem
Users sometimes make data entry errors while working. How can I allow users to undo changes they've made to data in unbound controls using Data Access Objects?
Technique
A few additional lines of code in the class module handling data management for your database can implement an undo feature. Because data is not updated in bound controls until you explicitly update it with your code, you can restore the original values by reloading them from the underlying recordset. The class module handles restoring the original values from the recordset and assigning them to the property values. The form only needs to read the data from the property procedures and write the property values to the controls.
Steps
Open and run the project HT203.VBP and the form shown in Figure 2.4 appears. Change the data in any control on the form, and use the Undo command on the Edit menu to restore the original value.
Figure 2.4. The Undo Browser form.
Table 2.3. Menu specifications for the Edit menu.
CAPTION Name Shortcut Key &Edit mnuEdit ----&Undo mnuEditUndo Ctrl+Z
NOTE By convention, the Edit menu is placed to the immediate right of the File menu.
Private Sub mnuEdit_Click()
` enable/disable undo command based on current dirty flag
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` toggle based on dirty flag
mnuEditUndo.Enabled = mclsTitles.IsDirty
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuEditUndo_Click()
` undo changes
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` undo changes
mclsTitles.UndoRecord
` refresh the display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Public Sub UndoRecord()
` clear changes and refresh properties
` clear dirty flag
` but do not clear new flag
mblnIsDirty = False
` refresh the current values from the recordset
GetCurrentRecord
End Sub
How It Works
Each of the property procedures that represent fields in the CTitles class sets the module-level mblnIsDirty flag. This flag is then used to toggle the enabled property of the Undo command on the Edit menu. When the user selects Undo, the form calls the UndoRecord method of the class and refreshes the controls on the form. The UndoRecord method needs only to restore the private module-level variables with the data that is still unchanged in the recordset.
Comments
The standard TextBox control supports a field level undo capability with the built-in context menu. By adding some code, you could also implement a field-level undo command for any field you display, regardless of the type of control that is used.
Problem
Viewing and editing existing records are only half of the jobs my users need to do. How do I add and delete records using unbound controls and Data Access Objects?
Technique
The recordset object provides the AddNew and Delete methods for inserting and deleting records. When you are using unbound controls on a form, adding a record is a five-step process:
Deleting a record is a two-step process:
ADD AND DELETE USER INTERFACESMicrosoft Access and (if properly configured) the Data control enable users to add new records by navigating to the end of the recordset and clicking the Next Record button. Although this two-step procedure might seem obvious to most programmers and database developers, it is not at all obvious to most users. Users will be looking for something clearly labeled as a command that will give them a new record with which to work. Rather than emulate this confusing idiom, this example uses a New Record command on the Data menu.
The same reasoning applies to deleting records. Provide a clearly labeled menu command or button the user can click to perform a delete.
In short, don't aggravate your users by burying common operations with obscure procedures in the interface.
Steps
Open and run the project HT204 .VBP. To add a new record, select the Data | New Record menu command, as shown in Figure 2.5. To delete a record, choose the Delete Record command.
Figure 2.5. The Add and Delete form.
Table 2.4. Menu specifications for Form1.
CAPTION Name Shortcut Key &File mnuFile ----E&xit mnuFileExit &Data mnuData ----&New Record mnuDataNewRecord Ctrl+N ----&Save Record mnuDataSaveRecord Ctrl+S ----&Delete Record mnuDataDeleteRecord Ctrl+X
Private Sub Form_Load()
` create the mclsTitles object and display the first record
On Error GoTo ProcError
Dim strDBName As String
Screen.MousePointer = vbHourglass
` create the CTitles object
Set mclsTitles = New CTitles
` fetch and display the current record
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
` An error was generated by Visual Basic or CTitles.
` Check for the "No Records" error and if so
` just provide a new record.
Select Case Err.Number
Case ErrNoRecords
mclsTitles.NewRecord
Resume Next
Case Else
` Some other error
` Display the error message and terminate gracefully.
MsgBox Err.Description, vbExclamation
Unload Me
Resume ProcExit
End Select
End Sub
Private Sub mnuData_Click()
` set enabled/disabled flags for menu commands
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` save enabled only when dirty
mnuDataSaveRecord.Enabled = mclsTitles.IsDirty
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuDataNewRecord_Click()
` set up a new record
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` save existing first
mclsTitles.SaveRecord
` get a new record
mclsTitles.NewRecord
` refresh display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuDataSaveRecord_Click()
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` save it
mclsTitles.SaveRecord
` refresh display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuDataDeleteRecord_Click()
` delete the current record
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
mclsTitles.DeleteRecord
` refresh display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
Select Case Err.Number
Case ErrNoRecords
` last record was deleted
` Create a new record
mclsTitles.NewRecord
Resume Next
Case Else
` inform
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Select
End Sub
Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Note: It's up to the client to save
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Flags
` dirty flag
Private mblnIsDirty As Boolean
` new record flag
Private mblnIsNew As Boolean
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
FirstRecord = 1
LastRecord = 2
NextRecord = 3
PreviousRecord = 4
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
ErrInvalidMoveType = vbObjectError + 1000 + 11
ErrNoRecords = vbObjectError + 1000 + 12
End Enum
Private Sub AddNewRecord()
` DAO AddNew/Update
` inform DAO we are going to insert
mrs.AddNew
` write the current values
mrs![ISBN] = mstrISBN
mrs![Title] = mstrTitle
mrs![Year Published] = mstrYearPublished
mrs![PubID] = mstrPubID
` update the record
mrs.Update
` return to the new record
mrs.Bookmark = mrs.LastModified
` clear new flag
mblnIsNew = False
` clear dirty flag
mblnIsDirty = False
End Sub
Public Property Get IsNew() As Boolean
` pass out the new flag
IsNew = mblnIsNew
End Property
Public Sub NewRecord()
` clear the current values for an insert
` NOTE: the flags work so that if a new
` record is added but not changed, you
` can move off of it or close with no
` prompt to save
` assign zero-length strings to the properties
mstrISBN = ""
mstrTitle = ""
mstrYearPublished = ""
mstrPubID = ""
` set the new flag
mblnIsNew = True
End Sub
Public Sub DeleteRecord()
` DAO delete
` delete the record
mrs.Delete
` clear new and dirty flags
mblnIsDirty = False
mblnIsNew = False
` reposition to a valid record
mrs.MovePrevious
` check for BOF
If mrs.BOF Then
` could be empty, check EOF
If Not mrs.EOF Then
mrs.MoveFirst
Else
` empty recordset, raise error
` the client must decide how to
` handle this situation
RaiseClassError ErrNoRecords
End If
End If
GetCurrentRecord
End Sub
Public Sub SaveRecord()
` save current changes
` test dirty flag
If mblnIsDirty Then
` test new flag
If mblnIsNew Then
` add it
AddNewRecord
Else
` update it
UpdateRecord
End If
Else
` record is already clean
End If
End Sub
How It Works
The CTitles class handles all the data processing with the database engine and provides a lightweight wrapper around the AddNew, Update, and Delete methods of the Recordset object. The form exposes these features in the user interface by providing menu commands for each data operation and handling save and error trapping logic.
Encapsulating all the recordset management logic in the class module means that all that code can be easily reused in other forms based on the same data.
Comments
A complete application might not necessarily have a one-to-one correspondence between tables and class modules. The classes should reflect the object model for the application and its data, not the database schema itself. The database underlying a complex application might have tables that are not reflected in the object model, such as tables used only for supplying lookup values to lists, or tables that are not directly represented, such as junction tables in many-to-many relationships.
Problem
Using Data Access Objects to build data entry forms with unbound controls works well in most situations, but sometimes the data is difficult for the user to work with. I need to extend the user interface of my application to provide alternative methods of finding and choosing records.
The nature of database applications is that users must often deal with less-than-obvious values, such as foreign keys and coded data. Instead of forcing the user to remember arbitrary key values and data codes, you can alter the user interface to provide lists of values rather than simple text boxes.
NOTE Chapter 4, "Designing and Implementing a Database," discusses foreign keys and table relationships.
Foreign key values can represent a special problem because the lookup tables for the keys are often quite large. Populating a list with all the available values can seriously damage the performance of the application. Additionally, because this is such a common operation, a generic tool for working with this type of data saves significant coding effort.
Using a simple form and a ListView control, you can build a generic tool that can display the results of an ad hoc query and return a key value for the record selected by the user.
Steps
Open and run the project HT205.VBP. Select Data|Find Publisher and enter SAMs. Click OK to display the results. The form shown in Figure 2.6 appears.
Figure 2.6. The Lookup Browser form.
Table 2.5. Menu specifications for Form1.
CAPTION Name Shortcut Key &File mnuFile ----E&xit mnuFileExit &Data mnuData ----&Save Record mnuDataSaveRecord Ctrl+S ----&Find Publisher mnuDataFindPublisher Ctrl+F
Private Sub mnuDataFindPublisher_Click() ` Use the FSearchResults form to find a pub id On Error GoTo ProcError Dim strPrompt As String Dim strInput As String Dim strSQL As String Dim fSearch As FSearchResults strPrompt = "Enter all or the beginning of _ "the publisher name:" strInput = InputBox$(strPrompt, "Search for Publishers") If strInput <> "" Then ` search strSQL = "SELECT * FROM Publishers " & _ "WHERE Name LIKE `" & strInput & "*';" Set fSearch = New FSearchResults ` Note: Search method does not return ` until the user dismisses the form fSearch.Search "PubID", strSQL, Me If Not fSearch.Cancelled Then txt(txtPubID).Text = fSearch.KeyValue End If End If ProcExit: ` release the search form reference Set fSearch = Nothing Exit Sub ProcError: MsgBox Err.Description, vbExclamation Resume ProcExitEnd Sub
Table 2.6. Objects and properties for the FSearchResults form.
OBJECT Property Value ListView Name lvwResults View 3 - lvwReport LabelEdit 1 - lvwManual CommandButton Name cmd Index 0 Caption OK Default True CommandButton Name cmd Index 1 Caption Cancel Cancel True
Option Explicit ` This form will run an ad hoc query and ` display the results in the list view control ` command button array constants Const cmdOK = 0 Const cmdCancel = 1 ` cancel property Private mblnCancelled As Boolean ` selected key value Private mvntKeyValue As Variant ` subitem index for key value Private mintItemIdx As Integer
Private Sub cmd_Click(Index As Integer)
If Index = cmdOK Then
mblnCancelled = False
Else
mblnCancelled = True
End If
Me.Hide
End Sub
Private Sub lvwResults_ItemClick(ByVal Item As ComctlLib.ListItem)
On Error GoTo ProcError
mvntKeyValue = Item.SubItems(mintItemIdx)
ProcExit:
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Public Property Get Cancelled()
Cancelled = mblnCancelled
End Property
Public Property Get KeyValue() As Variant
KeyValue = mvntKeyValue
End Property
Public Sub Search( _
strKeyField As String, _
strSQLStatement As String, _
frmParent As Form)
` run the specified query and populate the
` listview with the results
Dim strDBName As String
Dim lngOrdRecPos As Long
Dim db As Database
Dim rs As Recordset
Dim fld As Field
strDBName = BiblioPath()
Set db = DBEngine(0).OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strSQLStatement, _
dbOpenDynaset, dbReadOnly)
` test for no records
If Not rs.EOF Then
` create the ordinal position column
lvwResults.ColumnHeaders.Add , "Ordinal", "Record"
` set width
lvwResults.ColumnHeaders("Ordinal").Width = 600
` create the columns in the listview
For Each fld In rs.Fields
lvwResults.ColumnHeaders.Add , fld.Name, fld.Name
` best guess column width
lvwResults.ColumnHeaders(fld.Name).Width _
= 150 * Len(fld.Name)
If fld.Name = strKeyField Then
` mark the item index for later retrieval
mintItemIdx = fld.OrdinalPosition + 1
End If
Next ` field
` populate the list
Do
` increment the ordinal position counter
lngOrdRecPos = lngOrdRecPos + 1
` add the item
lvwResults.ListItems.Add _
lngOrdRecPos, , CStr(lngOrdRecPos)
` add the fields to the rest of the columns
For Each fld In rs.Fields
lvwResults.ListItems(lngOrdRecPos). _
SubItems(fld.OrdinalPosition + 1) = _
fld.Value & ""
Next ` field
` go to next record
rs.MoveNext
Loop While Not rs.EOF
` clean up
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
` show modally
Me.Show vbModal, frmParent
Else
` no data, treat as a cancel
mblnCancelled = True
MsgBox "No matching records found.", vbInformation
Me.Hide
End If
End Sub
How It Works
The FSearchResults form provides a generic tool for running an ad hoc query, displaying its results, and returning a key value selected by the user. The benefit of using a generic search form is that the form can be easily reused in any situation in which this type of lookup is required. This form can be added with minimal impact on the original design or performance of the main data entry form, but it still provides the user with a more advanced method of entering the PubID foreign key field.
The form works by filling a ListView control using a completely generic population routine in the Search method. When the user selects a record and dismisses the form by clicking OK, a private module-level variable retains the selected item, which can then be read from the KeyValue property.
Comments
Over time, you might build a significant library of generic components such as this lookup form. If the tools are properly designed and not coupled too tightly to any particular application or database, you might be able to bundle them together in an ActiveX DLL, which can then be included in future applications without having to return to the source code.
Problem
I know that indexes can be used to speed up database operations. How can I take advantage of indexes in my application?
Technique
When a table-type recordset has a current index (either the primary key or another index you have designated), you can use the Seek method to find records based on the indexed values. To use the Seek method, provide it with an argument that matches each field in the index. When the Seek method executes, if it finds at least one record matching the index values, it positions the record pointer to the first matching record and sets the NoMatch property of the recordset to False. If Seek does not find a matching record, it sets the NoMatch property to True; the current record is then undefined, which means that you can't be sure where the record pointer is pointing.
When you use Seek, specify not only the values for the key index fields but also the comparison criterion that Seek is to use. You provide the comparison criterion as the first argument to the Seek method, and you provide it as a string value. In the majority of cases, you will specify that Seek is to match the index value exactly; you do this by specifying a comparison criterion of =. You can also specify < > for not equal, > for greater than, < for less than, >= for greater than or equal to, or <= for less than or equal to.
Steps
Open and run the project HT206.VBP. Use the Index command on the Data menu to select the ISBN index. Browse forward in the recordset a few records, and copy the ISBN number from the form to the clipboard. Using the MoveFirst button, return to the first record; then select Data|Seek to display the input box shown in Figure 2.7. Paste the value you copied into the input box, and click OK. The record with the matching ISBN number is displayed.
Figure 2.7. The Seek form.
Table 2.7. Menu specifications for Form1.
CAPTION Name Shortcut Key &File mnuFile ----E&xit mnuFileExit &Data mnuData ----&Save Record mnuDataSaveRecord Ctrl+S ----&Index mnuDataIndex --------&ISBN mnuDataIndexName --------&Title mnuDataIndexName ----S&eek mnuDataSeek
Option Explicit ` CTitles object Private mclsTitles As CTitles ` These constants are used for the various control arrays ` command button constants Const cmdMoveFirst = 0 Const cmdMovePrevious = 1 Const cmdMoveNext = 2 Const cmdMoveLast = 3 ` text box index constants Const txtTitle = 0 Const txtYearPublished = 1 Const txtISBN = 2 Const txtPubID = 3 ` index constants Const idxISBN = 0 Const idxTitle = 1 ` refresh flag Private mblnInRefresh As Boolean
Private Sub mnuData_Click()
` set enabled/disabled flags for menu commands
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` seek enabled only if index is set
If Len(mclsTitles.IndexName) Then
mnuDataSeek.Enabled = True
Else
mnuDataSeek.Enabled = False
End If
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuDataIndexName_Click(Index As Integer)
` set the current index
On Error GoTo ProcError
Screen.MousePointer = vbHourglass
` set the index
Select Case Index
Case idxISBN
` assign the index
mclsTitles.Index = IndexISBN
` set up menu check marks
mnuDataIndexName(idxTitle).Checked = False
mnuDataIndexName(idxISBN).Checked = True
Case idxTitle
` assign the index
mclsTitles.Index = IndexTitle
` set up menu check marks
mnuDataIndexName(idxTitle).Checked = True
mnuDataIndexName(idxISBN).Checked = False
End Select
` refresh display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Private Sub mnuDataSeek_Click()
` seek a record
On Error GoTo ProcError
Dim strMsg As String
Dim strResult As String
Screen.MousePointer = vbHourglass
` prompt for a value
strMsg = "Enter a value to search for:"
strResult = InputBox$(strMsg)
` seek for the record
mclsTitles.SeekRecord strResult
` refresh display
GetData
ProcExit:
Screen.MousePointer = vbDefault
Exit Sub
ProcError:
MsgBox Err.Description, vbExclamation
Resume ProcExit
End Sub
Option Explicit
` The CTitles class provides a light wrapper
` around the database and record for the
` Titles table in the Biblio database
` Database and recordset objects
Private mdb As Database
Private mrs As Recordset
` Fields
` title
Private mstrTitle As String
` year - note use of string for
` assignment to text box
Private mstrYearPublished As String
` ISBN number
Private mstrISBN As String
` PubID - also a string
Private mstrPubID As String
` Move method constants
Public Enum CTitlesMove
FirstRecord = 1
LastRecord = 2
NextRecord = 3
PreviousRecord = 4
End Enum
` Index constants
Public Enum CTitlesIndex
IndexISBN = 0
IndexTitle = 1
End Enum
` Error constants
` Note: RaiseClassError method provides the strings
` because you cannot assign a string to an Enum
Public Enum CTitlesError
ErrRecordNotFound = vbObjectError + 1000 + 10
ErrInvalidMoveType = vbObjectError + 1000 + 11
ErrNoRecords = vbObjectError + 1000 + 12
ErrInvalidIndex = vbObjectError + 1000 + 13
End Enum
Private Sub Class_Initialize()
` open the database and recordset
Dim strDBName As String
` Get the database name and open the database.
` BiblioPath is a function in READINI.BAS
strDBName = BiblioPath()
Set mdb = DBEngine.Workspaces(0).OpenDatabase(strDBName)
` Open the recordset.
Set mrs = mdb.OpenRecordset( _
"Titles", dbOpenTable, dbSeeChanges, dbOptimistic)
` Raise an error if there is no data
If mrs.BOF Then
RaiseClassError ErrNoRecords
End If
` fetch the first record to the properties
GetCurrentRecord
End Sub
Private Sub RaiseClassError(lngErrorNumber As CTitlesError)
` Note: DAO errors are passed out as-is
Dim strDescription As String
Dim strSource As String
` assign the description for the error
Select Case lngErrorNumber
Case ErrRecordNotFound
strDescription = "The record was not found."
Case ErrInvalidMoveType
strDescription = "Invalid move operation."
Case ErrNoRecords
strDescription = "There are no records " _
& "in the Titles table."
Case ErrInvalidIndex
strDescription = "Invalid Index Name."
Case Else
` If this executes, it's a coding error in
` the class module, but having the case is
` useful for debugging.
strDescription = "There is no message for this error."
End Select
` build the Source property for the error
strSource = App.EXEName & ".CTitles"
` raise it
Err.Raise lngErrorNumber, strSource, strDescription
End Sub
Public Property Get IndexName() As String
IndexName = mrs.Index
End Property
Public Property Let Index(lngIndex As CTitlesIndex)
` unlike the field values, this is validated when assigned
Dim vntBookmark As Variant
` save a bookmark
vntBookmark = mrs.Bookmark
` assign the index
Select Case lngIndex
Case IndexISBN
mrs.Index = "PrimaryKey"
Case IndexTitle
mrs.Index = "Title"
Case Else
` invalid, raise an error
RaiseClassError ErrInvalidIndex
End Select
` return to old record
mrs.Bookmark = vntBookmark
End Property
Public Sub SeekRecord(strValue As String)
` seek to the indicated record based on the current index
Dim vntBookmark As Variant
` mark the current record
vntBookmark = mrs.Bookmark
` seek, the first operator is the comparison,
` the following represent the field(s) in the index
mrs.Seek "=", strValue
` check for match
If Not mrs.NoMatch Then
` found it, now fetch it
GetCurrentRecord
Else
` not found, return to prior location
mrs.Bookmark = vntBookmark
` raise the not found error
RaiseClassError ErrRecordNotFound
End If
End Sub
How It Works
The Seek method takes two or more parameters. The first parameter specifies the comparison operator (normally =), and the following parameters are the values for the fields in the index. The Index Property Let procedure in the CTitles class enables you to assign the current index for the recordset, and SeekRecord searches for a value. Both procedures use bookmarks to store records and, if necessary, return to the original record.
Comments
You cannot set indexes or use the Seek method with dynaset- or snapshot-type Recordset objects. To find a record in a dynaset or snapshot recordset, use one of the Find methods: FindFirst, FindNext, FindPrevious, or FindLast. Because these methods do not use indexes, they are much slower than Seek operations with table-type recordsets. You also cannot use Seek on remote server tables because these cannot be opened as table-type recordsets.
In most cases, it is much faster to create a new dynaset- or snapshot-type recordset than to use either a Find or the Seek method. You do this by building a SQL statement that includes a WHERE clause specifying the records you want to retrieve. If the database engine can find a useful index for the query, it uses that index to speed up the query.
See Chapter 3, "Creating Queries with SQL," for details on creating SQL statements, and Chapter 4, "Designing and Implementing a Database," for more information on choosing and defining indexes.
Problem
I need to know how many records are in a recordset I've created. For table-type recordsets, this is easy--I just use the value of the RecordCount property. But when I try this with a dynaset- or snapshot-type recordset, I can't predict what value will be returned. Sometimes it's the correct count, while other times it's not. How can I reliably determine the number of records in a dynaset- or snapshot-type recordset?
Technique
The system tables in a Microsoft Access database include information about the number of records in every table in the database. As records are added or deleted, the table is continuously updated by the Jet engine. You can determine the number of records in the table at any time by checking the RecordCount property of the TableDef. Unlike a table, dynaset- and snapshot-type recordsets are temporary recordsets. You can't obtain a record count by checking a TableDef.
You can retrieve the RecordCount property of a dynaset- or snapshot-type recordset, but the value it returns depends on several factors in addition to the number of records actually in the recordset. The only way the Jet engine can determine how many records are in a dynaset- or snapshot-type recordset is by counting them. To count them, the Jet engine has to move through the records, one by one, until it reaches the end of the recordset. When the Jet engine creates a dynaset- or snapshot-type recordset, however, it does not automatically count the records because counting the records in a large recordset could take a long time. If you retrieve the RecordCount property immediately after you create a dynaset- or snapshot-type recordset, therefore, you're guaranteed to get back one of two values: 0 if the recordset is empty or 1 if the recordset has at least one record.
To get an accurate count, your code must tell the Jet engine to count the records. Do this by executing the recordset's MoveLast method. After a MoveLast, you can retrieve the RecordCount with the confidence that the value is accurate. In the case of a dynaset-type recordset, if you add or delete records, the Jet engine keeps track of them for you, and any subsequent looks at RecordCount will give you the correct current count.
Steps
Open and run the project HT207.VBP. You will see the form shown in Figure 2.8.
This form shows the number of records in the BIBLIO.MDB Authors table. The first
box reports the number of records reported immediately after Authors is opened as
a table-type recordset. The second box shows the number of records reported immediately
after a dynaset is opened with the SQL statement SELECT Au_ID FROM Authors--a statement
that returns a dynaset consisting of the entire table, the same set of records that
are in the table-type recordset reported in the first box. The third box shows the
record count from the dynaset after its MoveLast method has been used. Note that
the first and third boxes have the same number (which might be different on your
system). The second box, reporting the dynaset record count before the MoveLast,
shows a count
of 1.
Figure 2.8. The Record Counts form.
Table 2.8. Objects and properties for Form1.
OBJECT Property Setting Form Name Form1 Caption Record Counter CommandButton Name cmdExit Cancel True Default True Caption Exit Label Name lblTable Alignment 2 (Center) BorderStyle 1 (Fixed Single) Label Name lblDynasetCreate Alignment 2 (Center) BorderStyle 1 (Fixed Single) Label Name lblDynasetMoved Alignment 2 (Center) BorderStyle 1 (Fixed Single) Label Name Label1 Caption Records reported in the BIBLIO.MDB Authors table recordset: Label Name Label2 Caption Records in the BIBLIO.MDB SELECT Au_ID FROM Authors dynaset immediately after creation. Label Name Label3 Caption Records in the BIBLIO.MDB SELECT Au_ID FROM Authors dynaset after using the MoveLast method.
Private Sub Form_Load()
On Error GoTo ProcError
Dim strDBName As String
Dim strSQL As String
Dim db As Database
Dim rsTable As Recordset
Dim rsDynaset As Recordset
` Get the database name and open the database.
` BiblioPath is a function in READINI.BAS
strDBName = BiblioPath()
Set db = DBEngine.Workspaces(0).OpenDatabase(strDBName)
Set rsTable = db.OpenRecordset("Authors", dbOpenTable)
lblTable = rsTable.RecordCount
strSQL = "SELECT Au_ID FROM Authors"
Set rsDynaset = db.OpenRecordset(strSQL, dbOpenDynaset)
lblDynasetCreate = rsDynaset.RecordCount
rsDynaset.MoveLast
lblDynasetMoved = rsDynaset.RecordCount
ProcExit:
On Error Resume Next
` clean up
rsDynaset.Close
rsTable.Close
db.Close
Exit Sub
ProcError:
MsgBox Err.Description
Resume ProcExit
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
How It Works
The table-type recordset enables you to check the RecordCount property immediately after it is created, but with a snapshot- or dynaset-type recordset, you must first access all the records before you can obtain an accurate count. This is typically done via the MoveLast method.
Comments
One common reason for wanting an accurate record count is to use the count as the control for a For...Next loop to cycle through the entire recordset, as in this code fragment:
myRecordset.MoveLast
n = myRecordset.RecordCount
myRecordset.MoveFirst
for i = 1 to n
` do something
myRecordset.MoveNext
next i
The difficulties with getting and keeping accurate record counts make it inadvisable to use code like this--especially in shared data environments where the record count of a table can change the instant after you retrieve it. This fragment illustrates a more reliable way to accomplish the same goal:
myRecordset.MoveFirst
Do While Not myRecordset.EOF
` do something
myRecordset.MoveNext
Loop
This loop executes until the last record in the recordset has been processed (when myRecordset.EOF becomes True), and it does not depend on a potentially unstable record count.
Problem
When I access a database through Visual Basic, I have limited control over the environment. A user might move a database file, or another program might have made unexpected changes to the database. I need my programs to be able to detect errors that occur and handle them in the context of the program. How do I accomplish this task with Visual Basic?
Technique
When an error occurs in a Visual Basic program, control passes to error-handling logic. Unless you have enabled an error handler, Visual Basic uses its default handler, which displays a message about the error--one that is sometimes useful, but often not--and terminates the application.
Clearly, the default handling is not acceptable. Fortunately, Visual Basic provides tools that you can use to build your own error traps and handlers. Although any Visual Basic application should trap and handle runtime errors, it is especially important in database applications in which many error conditions can be expected to occur. Visual Basic error-trapping is enabled with the On Error statement. The On Error statement takes two basic forms:
On Error Goto label On Error Resume Next
In the first form, when a runtime error occurs, Visual Basic transfers control of the application to the location specified by label. In the second form, Visual Basic continues execution with the line following the line in which the error occurred. When an error trap is enabled and an error occurs, Visual Basic performs the action indicated by the most recent On Error statement in the execution path. Listing 2.1 shows a hypothetical call tree and several variations of how error handlers are enabled and activated.
Sub SubA()
...other code
SubB
End Sub
Sub SubB()
On Error Goto ProcError
SubC
ProcExit:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Sub
SubC
...other code
SubD
End Sub
SubD()
On Error Resume Next
...code
End Sub
Understanding the path of execution in this code fragment is important to comprehending how error handlers are activated:
WARNING Errors in Class_Terminate events and most form and control events are fatal to your application if untrapped, so it is especially important that you include error-handling logic in these procedures.
Errors generated by Visual Basic and by components of an application--including the Jet database engine--are associated with error numbers. You can obtain the error number, which tells you the nature of the error, by reading the Number property of the Err object. You can read additional information about the error from the Description property. After you know the type of error, you can take appropriate action to handle it. This is typically done with a Select Case block.
NOTE For backward compatibility, Visual Basic still supports the outdated Err and Error statements and functions. However, any new code should use the Err object.
The code fragment in Listing 2.2 illustrates how you might handle some common errors that occur in a multiuser environment. (See Chapter 11, "The Windows Registry and State Information," for a complete discussion of working with a multiuser database application.)
Sub DAOCode()
On Error Goto ProcError
...code
ProcExit:
Exit Sub
ProcError
Dim strMsg As String
Select Case Err.Number
Case 3197
` Another user changed the data since the last time
` the recordset was updated
strMsg = "The data in this record was changed by " & _
"another user." & _
vbCrLf & "Do you want to overwrite those changes?"
If MsgBox(strMsg, vbYesNo or vbQuestion or vbDefaultButton2) _
= vbYes Then
` VB only generates the error on the first attempt
` Resume re-executes the line that caused the error
Resume
Else
` refresh the existing data
rs.Requery
DisplayData
Resume ProcExit
End If
Case 3260
` locked by another user
strMsg = "The record is currently locked by another user."
` control continues at end of block
Case Else
` default
strMsg = "Error: " & Err.Number & vbCrLf & Err.Description
End Select
MsgBox strMsg, vbExclamation
Resume ProcExit
End Sub
An error handler must execute a statement that clears the error. Table 2.9 list the methods of clearing an error.
| STATEMENT | Effect |
| Resume | Re-executes the line that generated the error |
| Resume Next | Resumes execution at the line that follows the line that generated the error |
| Resume label | Resumes execution at the line following the named label |
| Resume number | Resumes execution at the line with the indicated number |
| Exit Sub | Exits immediately from the current subroutine |
| Exit Function | Exits immediately from the current function |
| Exit Property | Exits immediately from the current property |
| On Error | Resets error-handling logic |
| Err.Clear | Clears the error without otherwise affecting program execution |
| End | Terminates execution of the program |
ERRORS IN CLASS MODULESIt is good programming practice to separate data management code in class modules from user interface code in forms. To maintain this separation, it is important that you do not simply display a message if an error occurs in a class module. Two types of error situations can occur in a class module.
A class can detect an error condition (such as the violation of a validation rule). In this case, the class module should call the Raise method of the Err object and set the Number, Description, Source, and--if a help file is available--the appropriate help properties.
A class can also trap errors raised by the database engine. These can be much more difficult to handle. The sheer number of possible errors makes it impractical in most applications to reassign and describe these errors, so most applications simply regenerate them.
In either case, the code in the class module should, if possible, attempt to correct the error before raising it.
Steps
Open and run the project HT208.VBP. Three errors will occur in succession. For each, the message reporting the error gives you the error number, error description, and line number where the error occurred. Figure 2.9 shows the first of these errors.
Figure 2.9. The HT208 error message.
Table 2.10. Objects and properties for Form1.
OBJECT Property Setting Form Name Form1 Caption Errors
Private Sub Form_Load()
On Error GoTo ProcError
Dim db As Database
Dim dbName As String
Dim rs As Recordset
Dim s As String
` Get the database name and open the database.
` BiblioPath is a function in READINI.BAS
5 dbName = BiblioPath()
10 Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)
20 Set rs = db.OpenRecordset("No Such Table", dbOpenTable)
30 Set rs = db.OpenRecordset("Titles", dbOpenTable)
40 s = rs![No Such Field]
50 rs.Edit
60 rs![Year Published] = "XYZ"
70 rs.Update
80 End
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & vbCrLf & _
"Line: " & Erl & vbCrLf & _
Err.Description, vbExclamation
Resume Next
End Sub
NOTE Line numbers are used here to help illustrate the error handler. Few programmers actually use them in production code, although they can be helpful for debugging.
How It Works
When Visual Basic encounters a runtime error, it transfers control of the application to the error-handling code you specify by using the On Error statement. You have a choice of inline handling using On Error Resume Next or centralized handling using On Error Goto. Either way, it's up to you to determine the type of error generated and the appropriate action to take for that error.
ERRORS COLLECTIONIt is possible for a single statement in DAO code to generate several errors. You can examine these errors by iterating the DAO Errors collection, as shown in the following code fragment:
For Each Error In Errors Debug.Print Err.Number & " - " & Err.Description Next ` Error
Comments
Hundreds of potential runtime errors can occur in a Visual Basic application, and you are unlikely to be able to anticipate all of them. With experience and careful coding, you can be prepared for the most common problems, but you should expect that from time to time your application will encounter a situation for which you did not explicitly plan. In this situation it is important for your general error-handling code to offer as much information as possible to the user and to provide the opportunity to correct the problem.
You might also find it useful to write errors to a log file. Examining an error log provides you with information you can use to build more robust error-handling procedures.
Problem
I have Excel worksheets that my company created. I need to incorporate this data into my own applications. I want to both display and manipulate the data from these Excel worksheets. How do I work with these Excel files in my Visual Basic projects?
Technique
By using the Microsoft Jet engine, you can access Excel worksheets as if they were actually Access databases. As with accessing other types of ISAM files with the Jet engine, there are a few restrictions with accessing Excel worksheets:
You can add records to a worksheet or edit standard cells (those without formulas).
When opening an ISAM database with the OpenDatabase method, you must provide a valid ISAM type. In addition to this, if you want to use the first row of the Excel document as field names, you can specify a parameter HDR equal to Yes. (HDR stands for header.) If you set HDR to No, the first row of the Excel worksheet is included as a record in the recordset, as in this example:
Set db = DBEngine.Workspaces(0).OpenDatabase("C:\VB6DBHT\" & _
"CHAPTER02\HowTo09\WidgetOrders.XLS", _
False, False, "Excel 8.0; HDR=NO;")
Notice that when accessing Excel worksheets, unlike with other ISAM formats, you must specify the filename in the OpenDatabase method.
When opening a recordset from an Excel ISAM, you must specify the sheet name as the recordset name, followed by a dollar sign ($), as in this example:
WorkSheetName = "Sheet1" Set rs = db.OpenRecordset(WorkSheetName & "$", dbOpenTable)
Here, rs is a recordset variable.
Steps
Open and run the ExcelDAO project. You should see a form that looks like the one shown in Figure 2.10. This application enables you to view a Microsoft Excel worksheet file in a ListView control. If you click the Add or Edit buttons, the form expands so that you can edit the contents. Clicking OK or Cancel when editing a record either saves or discards your changes, respectively. By clicking the View in Excel button, you can view the worksheet in Microsoft Excel (assuming that you have Microsoft Excel; it is not included on the distribution CD-ROM with this book). If you make changes in Excel and click the Refresh button on the form, the ListView control repopulates with the updates.
Figure 2.10. The ExcelDAO project.
Figure 2.11. The ExcelDAO form in design mode.
Table 2.11. Objects and properties for the Widget Orders project.
OBJECT Property Setting Form Name frmWidgetOrders Caption Widget Orders Height 3390 List view Name lstvWidgetOrders View 3 `vwReport Command button Name cmdAdd Caption &Add Command button Name cmdEdit Caption &Edit Command button Name cmdView Caption &View in Excel Command button Name cmdRefresh Caption &Refresh Command button Name cmdClose Caption &Close Cancel True Default True Command button Name cmdOk Caption &Ok Command button Name cmdCancel Caption &Cancel Text box Name txtOrderNum Text box Name txtProductID Text box Name txtProductDesc Text box Name txtQuantity Text box Name txtUnitPrice Label Name lblOrderNum Caption Order Num Label Name lblProductID Caption Product ID Label Name lblProductDesc Caption Product Description Label Name lblQuantity Caption Quantity Label Name lblUnitPrice Caption Unit Price
Option Explicit ` form-level variables used to hold the database and recordset Private db As Database Private rs As Recordset ` form-level constant values used to indicate the current state Private Const ADD_RECORD = 0 Private Const EDIT_RECORD = 1 ` form-level variables used to save the current state, and ` selected list item Private m_nState As Integer Private m_oSelItem As ComctlLib.ListItem ` form-level variables used to store the file path and sheet name ` of the Excel file used in the app Private m_sFilePath As String Private m_sSheetName As String
Private Sub Form_Activate()
` allow app to paint screen
DoEvents
` get paths and names of files used in app
m_sFilePath = DataPath & "\Chapter02\WidgetOrders.xls"
m_sSheetName = "Sheet1$"
` populate the list view control
PopulateListView
End Sub
Private Sub cmdAdd_Click()
` clear all the text boxes
txtOrderNum = ""
txtProductID = ""
txtProductDesc = ""
txtQuantity = ""
txtUnitPrice = ""
` show the bottom of the form and set the state to add so we
` know how to save the record later
ShowBottomForm
m_nState = ADD_RECORD
End Sub
Private Sub cmdEdit_Click()
` we cannot use indexes with Excel files, so we must
` transverse the recordset until the record matches the
` selected item, then populate the text boxes with the records
` values
With rs
.MoveFirst
While (.Fields("Order Number") <> m_oSelItem.Text)
.MoveNext
Wend
txtOrderNum = .Fields("Order Number")
txtProductID = .Fields("Product ID")
txtProductDesc = .Fields("Product Description")
txtQuantity = .Fields("Quantity")
txtUnitPrice = .Fields("Unit Price")
End With
` show the bottom of the form and set the state to editing so
` we know how to save the record later
ShowBottomForm
m_nState = EDIT_RECORD
End Sub
Private Sub cmdView_Click()
` set the recordset and database to nothing because Excel will
` not be able to successfully open the file if not
Set rs = Nothing
Set db = Nothing
` open Excel with the file
Shell ExcelPath & " """ & m_sFilePath & """", vbNormalFocus
End Sub
Private Sub cmdRefresh_Click()
` force a repopulation of the list view (use when the user has
` made changes in Excel to the file)
PopulateListView
End Sub
Private Sub cmdClose_Click()
` always use Unload Me instead of End
Unload Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
` it is good practice to set all objects to nothing
Set m_oSelItem = Nothing
` this is equivalent to closing the recordset and the database
Set db = Nothing
Set rs = Nothing
End Sub
Private Sub cmdOk_Click()
` edit or add new is confirmed, save the values of the text
` boxes this would be a good place to code validation for each
` field
With rs
If (m_nState = ADD_RECORD) Then
.AddNew
Else
.Edit
End If
.Fields("Order Number") = txtOrderNum
.Fields("Product ID") = txtProductID
.Fields("Product Description") = txtProductDesc
.Fields("Quantity") = txtQuantity
.Fields("Unit Price") = txtUnitPrice
.Fields("Total Price") = txtUnitPrice * txtQuantity
.Update
End With
` repopulate the listview with the changes; then hide the
` bottom of the form
PopulateListView
HideBottomForm
End Sub
Private Sub cmdCancel_Click()
` edit or add new was canceled, hide the bottom of the form
HideBottomForm
End Sub
Private Sub ShowBottomForm()
` lengthen the height of the form and enable the proper
` controls
Me.Height = 4350
SetObjects False
End Sub
Private Sub HideBottomForm()
` shorten the height of the form and enable the proper
` controls
Me.Height = 3390
SetObjects True
End Sub
Private Sub SetObjects(StateIn As Boolean)
` set Enabled property for controls on top of form
cmdAdd.Enabled = StateIn
cmdEdit.Enabled = StateIn
cmdRefresh.Enabled = StateIn
cmdView.Enabled = StateIn
cmdClose.Enabled = StateIn
` set Enabled property for controls on bottom of form
txtOrderNum.Enabled = Not StateIn
txtProductID.Enabled = Not StateIn
txtProductDesc.Enabled = Not StateIn
txtQuantity.Enabled = Not StateIn
txtUnitPrice.Enabled = Not StateIn
cmdOk.Enabled = Not StateIn
cmdCancel.Enabled = Not StateIn
End Sub
Private Sub PopulateListView()
Dim oField As Field
Dim nFieldCount As Integer
Dim nFieldAlign As Integer
Dim nFieldWidth As Single
Dim oRecItem As ListItem
Dim sValFormat As String
` this might take a noticeable amount of time, so before we do
` anything change the mouse pointer to an hourglass and then
` hide the bottom of the form
Screen.MousePointer = vbHourglass
HideBottomForm
` open the database (this might already be open; however, if
` the user has just started the app or selected the `View in
` Excel' button, then the database and recordset would be set
` to nothing
Set db = OpenDatabase(m_sFilePath, False, False, _
"Excel 8.0;HDR=YES;")
Set rs = db.OpenRecordset(m_sSheetName)
With lstvWidgetOrders
` clear the list view box in case this is a refresh of the
` records
.ListItems.Clear
.ColumnHeaders.Clear
` using the For Each statement as compared to the For To
` statement is technically faster, as well as being
` easier to understand and use
For Each oField In db.TableDefs(m_sSheetName).Fields
` align currency fields to the right, all others to
` the left
nFieldAlign = IIf((oField.Type = dbCurrency), _
vbRightJustify, vbLeftJustify)
` our product description field is text, and the
` values in this field are generally longer than their
` field name, so increase the width of the column
nFieldWidth = TextWidth(oField.Name) _
+ IIf(oField.Type = dbText, 500, 0)
` add the column with the correct settings
.ColumnHeaders.Add , , oField.Name, _
nFieldWidth, _
nFieldAlign
Next oField
End With
` add the records
With rs
.MoveFirst
While (Not .EOF)
` set the new list item with the first field in the
` record
Set oRecItem = lstvWidgetOrders.ListItems.Add(, , _
CStr(.Fields(0)))
` now add the rest of the fields as subitems of the
` list item
For nFieldCount = 1 To .Fields.Count - 1
` set a currency format for fields that are
` dbCurrency type
sValFormat = IIf(.Fields(nFieldCount).Type =
dbCurrency, _
"$#,##0.00", _
"")
` set the subitem
oRecItem.SubItems(nFieldCount) = _
Format$("" & .Fields(nFieldCount), _
sValFormat)
Next nFieldCount
.MoveNext
Wend
End With
` by setting the last record item to the selected record item
` form variable, we can assure ourselves that a record
` is selected for editing later
Set m_oSelItem = oRecItem
` remember to set object variables to nothing when you are
` done
Set oRecItem = Nothing
Set oRecItem = Nothing
Screen.MousePointer = vbDefault
End Sub
How It Works
In this project, an Excel worksheet file is opened and used to create a recordset object to populate a ListView control. The code for accessing the recordset is the same as that of accessing Microsoft Access databases, using AddNew and Edit to alter the underlying ISAM data file. However, Excel worksheet rows cannot be deleted; therefore, the Delete method of a recordset is unavailable.
In the PopulateListView routine, the project uses the TableDef object of the database object to access the table definition of the Excel worksheet. Within this object, there is a collection of fields through which the project loops, adding a column header for each, using the fields' Name, Width, and Align properties.
After the column header collection of the list view is populated with the field names in the Excel worksheet, the ListView control is populated with the records. Accessing these records is the same as accessing other Jet database records. A list item is set for each record with the first field of the record. After this, the subitems of the list item are populated with the remaining fields in the current record.
This application also uses a trick to gain more space when necessary. The form is elongated when either the Add button or the Edit button is clicked, allowing room for a record editing area. After the record is saved or canceled, the form resumes its normal size.
Another feature of this project is that the user can view the worksheet in Excel by using the Shell method to start another application. By using this option, the user can load a worksheet, edit its changes, and then switch to Excel to see the results. Even if the user changes the worksheet within Excel while the project is still running (as long as the file is saved and the user clicks the Refresh button), the ListView control will be repopulated with the correct, up-to-date information.
Comments
It is possible to open ranges of an Excel worksheet using the Microsoft Jet Engine. To do this, replace the worksheet name in the OpenRecordset method with the range of cells you want returned, as in this example:
Set rs = db.OpenRecordset("B1:H12")
In this example, "B1:H12" is the range on which you want to create a recordset, from the specified Excel worksheet.
© Copyright, Macmillan Computer Publishing. All rights reserved.