Custom Project Taking Persisted Data on the Road

This custom project requires that you complete Hands-On 16-2. Part 1: Saving a Recordset to Disk

Before you can take a recordset on the road with you, you must save the records to a disk file. To create the data for this project, prepare and run the procedure in Hands-On 16-2. You should have the Companies.rst file available on your computer prior to going to Part 2.

Part 2: Creating an Unbound Access Form to View and Modify Data

Once you've saved the recordset to a disk file, the recordset becomes portable. You can take the file with you on the road or send it to someone else. But, before either one of you can view the data and modify it, you need some sort of a user interface. In this part, you will create an unbound Access form that will enable you to work with the file that contains the saved recordset.

ÜI] Disconnected combo




Alfreds Futterkiste

Ana Trujillo Emparedados y helados

Antonio Moreno Taquería

Around ihe Horn

Bergluxte snabbküp

Btauer See Delikatessen

Btondel pére et fits

Bóíido Comidas preparadas

Figure 16-2: After opening the form prepared in Custom Project 16-1, the combo box is filled with the names of companies obtained via a persisted recordset.

Part II

1. Create a form as shown in Figure 16-3. Notice that this form contains only a couple of fields from the Customers table. This form serves only as an example. You can use as many fields as you have saved in the disk file.

Figure 16-3: This custom form is used to demonstrate how you can use the saved recordset in an unbound form.

2. Set the following properties for the form's controls:






Company Name:

Text box next to the Company



Name label




Text box next to the City label



Back Color

Select any color you like




Text box next to the Country label








Command button 1





Command button 2





Command button 3





Command button 4





^^ Note: We have set the Back Color property of the txtCity text box in the example application to visually indicate that the user can update only this field's data.

Creating and Manipulating Databases with ADO

3. To visually match the form in Figure 16-3, draw a rectangle control over the command buttons and set its Back Color property to any color you like. Select the rectangle and choose Format | Send to Back to move the rectangle behind the command buttons.

4. Select the form by choosing Edit | Select Form and activate the form's properties sheet to set the following properties:

Property Name


Scroll Bars


Record Selectors


Navigation Buttons


5. Save the form as frmCompanyInfo.

Part 3: Writing Procedures to Control the Form and Its Data

Now that you've designed the form for your data, you need to write a couple of VBA procedures. The first procedure you'll write is an event procedure for the Form_Load event. This procedure will load the form with data from the persisted file. You will start by declaring a module-level Recordset object variable called rst and a module-level Integer variable called counter. You will also write Click procedures for all the command buttons and a procedure to fill the text boxes with the data from the current record in the recordset. Let's get started!

1. In the form's properties sheet, activate the Event tab and click the Build button next to the On Load event name. In the Choose Builder dialog box, select Code Builder and click OK.

2. Enter the code for the Form_Load event procedure as shown below, starting with the declaration of module-level variables:

Option Compare Database Dim rst As ADODB.Recordset Dim counter As Integer

Private Sub Form_Load()

Dim strFileName As String strFileName = CurrentProject.Path & "\" & "Companies.rst" On Error GoTo ErrorHandle

Set rst = New ADODB.Recordset With rst

.CursorLocation = adUseClient

.Open strFileName, , adOpenKeyset, adLockBatchOptimistic, adCmdFile End With counter = 1

Call FillTxtBoxes(rst, Me)

Part II

Me.txtCompany.SetFocus Me.cmdFirst.Enabled = False Me.cmdPrevious.Enabled = False Me.lbRecordNo.Caption = counter ExitHere:

Exit Sub ErrorHandle:

MsgBox Err.Number & ": " & Err.Description Resume ExitHere End Sub

3. The Form_Load event procedure loads Companies.rst from a disk file. To fill the text boxes with the data from the current record in the recordset, you need to write the following code:

Me.txtCompany = rst!CompanyName Me.txtCity = rst!City Me.txtCountry = rst!Country

Because the above code will need to be entered in several procedures in this application, you can save yourself a great deal of typing by placing this code in a subroutine and calling it like this:

Call FillTxtBoxes(rst, Me)

The above statement calls the subroutine named FillTxtBoxes and passes it two arguments: the Recordset object variable and the reference to the current form. The FillTxtBoxes subroutine is entered in a standard module and contains the code shown in the next step.

The counter variable, which was declared at the module level, is initialized to the value of 1. We will use this variable to control the display of command buttons on the form. The Form_Load event procedure ends by setting the focus to the first text box (Company) and disabling the first two command buttons. These buttons will not be required when the form first opens on the first record.

4. In the Visual Basic Editor Code window, choose Insert | Module and type the code of the FillTxtBoxes procedure as shown below:

Sub FillTxtBoxes(ByVal rst As ADODB.Recordset, frm As Form) With frm

.txtCompany = rst!CompanyName .txtCity = rst!City .txtCountry = rst!Country End With End Sub

The procedure above fills the three text boxes placed on the form with the data from the current record in the recordset. This procedure is called from the Form_Load event procedure and the Click event procedures for each command button.

Creating and Manipulating Databases with ADO

5. Write the following Click event procedure for the First command button:

Private Sub cmdFirst_Click()

On Error GoTo Err_cmdFirst_Click rst.Update "City", Me.txtCity rst.MoveFirst

Call FillTxtBoxes(rst, Me)

Me.txtCompany.SetFocus Me.cmdFirst.Enabled = False Me.cmdLast.Enabled = True Me.cmdPrevious.Enabled = False Me.cmdNext.Enabled = True counter = 1

Me.lbRecordNo.Caption = counter Exit_cmdFirst_Click: Exit Sub


MsgBox Err.Description Resume Exit_cmdFirst_Click End Sub

6. Write the following Click event procedure for the Next command button:

Private Sub cmdNext_Click()

On Error GoTo Err_cmdNext_Click rst.Update "City", Me.txtCity rst.MoveNext counter = counter + 1

Me.cmdFirst.Enabled = True

Call FillTxtBoxes(rst, Me)

Me.cmdPrevious.Enabled = True Me.lbRecordNo.Caption = counter Me.txtCompany.SetFocus If counter = rst.RecordCount Then Me.cmdNext.Enabled = False Me.cmdLast.Enabled = False End If

Exit_cmdNext_Click: Exit Sub


MsgBox Err.Description Resume Exit_cmdNext_Click End Sub

Part II

7. Write the following Click event procedure for the Previous command button:

Private Sub cmdPrevious_Click()

On Error GoTo Err_cmdPrevious_Click rst.Update "City", Me.txtCity rst.MovePrevious counter = counter - 1

Call FillTxtBoxes(rst, Me)

Me.txtCompany.SetFocus Me.cmdLast.Enabled = True Me.cmdNext.Enabled = True

Me.lbRecordNo.Caption = counter If counter = 1 Then

Me.cmdFirst.Enabled = False Me.cmdPrevious.Enabled = False End If

Exit_cmdPrevious_Click: Exit Sub

Err_cmdPrevious_Click: MsgBox Err.Description Resume Exit_cmdPrevious_Click End Sub

8. Write the following Click event procedure for the Last command button:

Private Sub cmdLast_Click()

On Error GoTo Err_cmdLast_Click rst.Update "City", Me.txtCity rst.MoveLast

Call FillTxtBoxes(rst, Me)

Me.txtCompany.SetFocus Me.cmdFirst.Enabled = True Me.cmdPrevious.Enabled = True Me.cmdLast.Enabled = False Me.cmdNext.Enabled = False counter = rst.RecordCount Me.lbRecordNo.Caption = counter Exit_cmdLast_Click: Exit Sub


MsgBox Err.Description Resume Exit_cmdLast_Click End Sub

Creating and Manipulating Databases with ADO

Notice that all the Click event procedures you prepared in steps 5-8 above contain the following line of code:

rst.Update "City", Me.txtCity

This statement updates the value of the City field in the recordset with the current value found in the txtCity text box on the form as you move through the records. Although the user can enter data in other text boxes, all modifications are ignored as there is no code in the Click event procedures that will allow changes to fields other than City. Of course, you can easily change this behavior by adding the necessary lines of code.

Depending on which button was clicked, certain command buttons are disabled and others are enabled. This gives the user a visual clue of what actions are allowed at a particular moment.

To make the form work, we need to write one more event procedure. Before closing the form, we must make sure that the changes to the City field in the current record are saved and all changes in the City field we made while working with the form data are written back to the disk file. In other words, we must replace the Companies.rst disk file with a new file. This is done in the Form_Unload event procedure as shown in step 9.

9. Write the code of the Form_Unload event procedure as shown below:

Private Sub Form_Unload(Cancel As Integer)

If rst.Fields("City").OriginalValue <> Me.txtCity Then rst.Update "City", Me.txtCity

End If

Kill (CurrentProject.Path & "\Companies.rst") rst.Save CurrentProject.Path & "\Companies.rst", _ adPersistADTG

End Sub

The ADO recordsets have a special property called OriginalValue, which is used for storing original values that were retrieved from a database. These original values are left unchanged while you edit the recordset offline. Any changes to the data made locally are recorded using the Value property of the Recordset object. The OriginalValue property is updated with the values changed locally when you reconnect to the database and perform an UpdateBatch operation (see Part 5 of Custom Project 16-2).

The Form_Unload event occurs when you attempt to close a form, but before the form is actually removed from the screen. This is a good place to perform those operations that must be executed before the form is closed. In the Form_Unload procedure above, we use the recordset's OriginalValue property to check whether changes were made to the content of the City field in the current record. If OriginalValue is different from the value found in the current record's txtCity text box, we want to save the record by using the Update method of the recordset. Next, we delete the file containing the original recordset and save the current recordset to a file with the same name.

Part II

Part 4: Viewing and Editing Data Offline

Now that you've written all the procedures for the custom application, let's begin using the form to view and edit the data.

1. Open the frmCompanyInfo form.

2. In the first record, replace Berlin with Drezden.

3. Click the Last button, and replace Warszawa with Opole.

4. Click the First button and notice that the value of City is Drezden, just as you changed it in step 2.

5. Use the Next button to move to the fourth record and replace London with Dover.

6. Close the form and then reopen it. Check the values in the City text box in the first, fourth, and last records. You should see Drezden, Dover, and Opole.

Part 5: Connecting to a Database to Update the Original Data

After you've made changes to the data by using the custom form, you can send the file with the modified recordset to your database administrator so that he or she can update the underlying database with your changes. Let's proceed to write a procedure that will take care of this task.

^^ Note: The procedure that you are about to write will modify the Customers table in the Northwind database. I recommend that you take few minutes now and create a copy of this database so that you can restore the original data later if necessary.

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the UpdateDb procedure shown below.

Sub UpdateDb()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strNorthPath As String Dim strRecStat As String

On Error GoTo ErrorHandle strNorthPath = "C:\Program Files\Microsoft Office\" & _ "Office11\Samples\Northwind.mdb"

' Open the connection to the database Set conn = New ADODB.Connection With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source = " & strNorthPath .Mode = adModeReadWrite .Open End With

Open the recordset from the local file

Creating and Manipulating Databases with ADO

' that was persisted to the hard drive ' and update the data source with the changes Set rst = New ADODB.Recordset With rst

.CursorLocation = adUseClient

.Open CurrentProject.Path & "\Companies.rst", conn, _

adOpenKeyset, adLockBatchOptimistic, adCmdFile .UpdateBatch adAffectAll

' Check if there were records with conflicts ' during the update .Filter = adFilterAffectedRecords Do Until .EOF

strRecStat = strRecStat & rst!City & ":" & rst.Status .MoveNext Loop .Close

Debug.Print strRecStat End With


Set rst = Nothing Set conn = Nothing Exit Sub


MsgBox Err.Number & ": " & Err.Description Resume ExitHere

End Sub

In the UpdateDb procedure shown above, we used the UpdateBatch method of the ADO Recordset object to update the underlying database with the changes we made to the data while working with it offline. The UpdateBatch method takes an optional parameter that determines how many records will be affected by the update. This parameter can be one of the constants shown in Table 16-1.

Table 16-1: Enumerated constants used with the UpdateBatch method






Pending changes will be written only for the current record.



Pending changes will be written for the records that satisfy the current filter.



Pending changes will be written for all the records in the recordset. This is the default.

When you update the data, your changes are compared with values that are currently in the database. The update will fail if the record was deleted or updated in the underlying database since the recordset was saved to disk. Therefore, after calling the UpdateBatch method, you should check the status of the records to locate records with conflicts. To do this, we must filter the recordset to see only the affected records:

rst.Filter = adFilterAffectedRecords

Part II

Next, we loop through the recordset and check the Status property of each record. This property can return different values, as shown in Table 16-2. You can locate these values in the Object Browser by typing RecordStatusEnum in the Search box.

Table 16-2: RecordStatusEnum constants returned by the Status property






The record was not saved because the operation was cancelled.



The new record was not saved because the existing record was locked.



The record was not saved because optimistic concurrency was in use.



The record has already been deleted from the data source.



The record was deleted.



The record was not saved because the user violated integrity constraints.



The record was not saved because its bookmark is invalid.



The record was not saved because there were too many pending changes.



The record was modified.



The record was not saved because it would have affected multiple records.



The record is new.



The record was not saved because of a conflict with an open storage object.



The record was successfully updated.



The record was not saved because the computer has run out of memory.



The record was not saved because it refers to a pending insert.



The record was not saved because the user has insufficient permissions.



The record was not saved because it violates the structure of the underlying database.



The record was not modified.

If the updates occurred without any conflict, the Status property will return the value of 8 (adRecUnmodified). While iterating through the recordset you can add additional code to resolve any encountered conflicts or check, for example, the original value and the updated value of the fields in updated records. As mentioned earlier, the OriginalValue property returns the field value that existed prior to any changes (since the last Update method was called). You can cancel all pending updates by using the CancelBatch method.

3. Run the UpdateDb procedure to write your changes to the database.

Creating and Manipulating Databases with ADO

4. Open the Northwind database and review the content of the City field in the Customers table. You should see Drezden, Dover, and Opole in the first, fourth, and last record.

5. Close the Northwind database.

This completes Custom Project 16-2 in which you learned how to:

■ Save the recordset to disk with the Save method

Create a custom form to view and edit the recordset data in the disk file

■ Open the recordset from disk with the Open method Work with the recordset offline (view and edit data)

Reopen the connection to the original database and write your changes with the UpdateBatch method

^^ Note: Refer to Part V of this book to find out how you can save a recordset in XML format using the adPersistXML format.

0 0

Post a comment