Data Management

How do I... Pass data over a network using disconnected recordsets?

Dragging only the data you actually need and only when you need it is the key to working efficiently across a network, but there's more. In Microsoft Access, you can also retrieve data using a disconnected recordset, which keeps the data on the client side, so objects respond quicker. Susan Harkins shows you how to set it up.

Retrieving data across a busy network can put a real cramp in your networking performance. Displaying the right data isn't enough -- you must display it promptly and on demand. Dragging only the data you actually need and only when you need it is the key to working efficiently across a network, but there's more. You can also retrieve data using a disconnected recordset, which keeps the data on the client side, so objects respond quicker.

This blog post is also available in PDF form as a TechRepublic download, which includes a sample database using the techniques outlined including the Access form described.

Sometimes hanging up is polite

The nature of Microsoft Access is to work with bound objects. Any change you make via a bound control updates the control's underlying data source. Access is famous for this arrangement, and it certainly makes things easy -- that is, as long as the data you're working with is in the same database.

Usually, a multi-user database is split into two databases. Users work with a local copy of the user interface in what's known as a front-end database, while the data is in a second file, known as a back-end database on a server. Even under the best of circumstances, the front-end objects perform slower. If the network is busy -- and they're all busy -- bound controls and forms can seemingly take forever to display data.

Access 2002 (and later versions) offers a powerful and flexible solution to network traffic. You can use a disconnected recordset to connect to a data source, retrieve data, and then hang up. The recordset still exists, but on the local system. You can manipulate the data in your database, instead of retrieving and updating via a network connection. Since the data is local, the user interface performs quickly.

Disconnected basics

An ActiveX Data Object (ADO) disconnected recordset performs like any other recordset. The difference is that you can modify data and then send those updates to a data source, without maintaining a connection on the server. You can send updates to any server, not just to the server (data) from which you initially retrieved the data. The only requirement is that both ends of the connection -- the client and the server -- have a compatible version of ADO installed.

When working with a disconnected recordset, keep the following in mind:

  • Use the client's cursor engine by setting the CursorLocation property to adUseClient before you open the recordset. That's how the recordset ends up on the users' systems.
  • If you want to make changes to the data, set the LockType property to adLockBatchOptimistic.
  • After retrieving the data, set the recordset object to Nothing to break the connection.
  • When you add, delete, and modify data, you're only changing the recordset. To update data via a connection, use the UpdateBatch method.

A disconnected example

The example takes one shortcut. Instead of trying to simulate a generic networked connection, it retrieves data from the local system. That way, the code concentrates on the disconnected recordset requirements instead of connection strings. Adjust the connection statements when applying this code to your networked environment.

The form in Figure A uses a disconnected recordset to populate a filtering combo box control. Once a user selects a customer, code populates the form by retrieving corresponding data from the same disconnected recordset.

Figure A

Both the combo box and the form controls use a disconnected recordset
The form is based on the Customers table in Northwind (the sample database that comes with Access). Refer to Table A for the form and control nondefault property settings. Neither the form nor any of the controls are bound. The filtering combo box control is in the form's header. The two updating command buttons are in the footer.

Table A

Object

Property

Setting

form Caption Customers
Scroll Bars Neither
Record Selectors No
Navigation Buttons No
combo box Name cboCompanyName
Column Count 2
Column Widths 0"
text box Name txtCustomerID
text box Name txtContactName
text box Name txtContactTitle
text box Name txtAddress
text box Name txtCity
text box Name txtRegion
text box Name txtPostalCode
text box Name txtCountry
text box Name txtPhone
text box Name txtFax
command button Name cmdSave
Caption Save
command button Name cmdUpdateServer
Caption Update Server

Notice that the control names are very similar to the actual fields in the Customers table. When possible, use the corresponding field names to name the controls. Doing so is self-documenting and will make the code much easier to write and maintain.

Once you have a form, you're ready to add the Visual Basic for Application (VBA) code. With the form in Design view, click the Code button to launch the form's module. In the General Declaration section, enter the following declaration to make the recordset a module-level object:

Dim CustomerDisconnectedRS As ADODB.Recordset

When you open the form, this statement will declare the recordset object. The form's Open event will then retrieve and populate the object and sever the connection to the server. This recordset remains in memory until you close the form, when the Close form destroys it. That means this recordset is available to all procedures in the module.

Next, enter the event procedures in Listing A through Listing E. Return to Access, save the form, and close it.

Listing A

Private Sub cboCompanyName_AfterUpdate()

'Populate form with filtered results.

On Error GoTo ErrHandler

Dim strCriteria As String

strCriteria = "CustomerID = '" & Me!cboCompanyName.Value & "'"

Debug.Print strCriteria

With CustomerDisconnectedRS

If Not (.EOF) Then

.Find strCriteria

Me.txtCustomerID = !CustomerID

Me.txtContactName = !ContactName

Me.txtContactTitle = !ContactTitle

Me.txtAddress = !Address

Me.txtCity = !City

Me.txtRegion = !Region

Me.txtCountry = !Country

Me.txtPhone = !Phone

Me.txtFax = !Fax

End If

End With

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub

Listing B

Private Sub cmdSave_Click()

On Error GoTo ErrHandler:

With CustomerDisconnectedRS

!CustomerID = Me.txtCustomerID

!ContactName = Me.txtContactName

!ContactTitle = Me.txtContactTitle

!Address = Me.txtAddress

!City = Me.txtCity

!Region = Me.txtRegion

!Country = Me.txtCountry

!Phone = Me.txtPhone

!Fax = Me.txtFax

End With

cmdUpdateServer.Enabled = True

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub

Listing C

Private Sub cmdUpdateServer_Click()

Dim cnn As New ADODB.Connection

On Error GoTo ErrHandler

'----------

'Example connection is to active connection.

'Set connection here for foreign or networked data.

'----------

Set cnn = Application.CurrentProject.Connection

With CustomerDisconnectedRS

.ActiveConnection = cnn

.UpdateBatch

End With

cmdUpdateServer.Enabled = False

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub

Listing D

Private Sub Form_Close()

Set CustomerDisconnectedRS = Nothing

End Sub

Listing E

Private Sub Form_Open(Cancel As Integer)

'Populate disconnected recordset object with customer information.

'Populate cboCompanyName with CustomerID and CompanyName.

Dim cnn As ADODB.Connection

Dim strSQL

On Error GoTo ErrHandler

Set CustomerDisconnectedRS = New ADODB.Recordset

'----------

'Example connection is to active connection.

'Set connection here for foreign or networked data.

'----------

Set cnn = Application.CurrentProject.Connection

strSQL = "SELECT * FROM Customers"

With CustomerDisconnectedRS

.CursorLocation = adUseClient

.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic

'Disconnect.

Set cnn = Nothing

End With

'Populate combo box.

Set cboCompanyName.Recordset = CustomerDisconnectedRS

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub

Opening the form in Form view triggers the form's Open event, which populates the recordset object. After retrieving the data from the local Customers table, the code breaks the connection by setting the connection object (cnn) to Nothing. Don't confuse this with the recordset, which remains open until the form's Close event destroys it.

The code populates the filtering combo box with the values from the CustomerID and CompanyName fields, although the list displays only the company names, as shown in Figure B. Because the recordset is a module-level object, you don't have to populate the combo box here, but it's a good spot for our purposes.

Figure B

Code retrieves values from a disconnected recordset to populate the control's list.

Keep in mind that the example recordset code uses properties that allow updates. When populating a filtering-type list control, you won't want to update the data source, so you might want to set the LockType property to acLockReadOnly --  but only if you're not using the same recordset to update.

To populate the form, select an item from the control's list. For instance, select Around the Horn (the fourth item). Doing so executes the control's After Update event, which uses the selected value to find a matching record in the disconnected recordset.

Remember, the list control's bound column is the CustomerID value and not the company name value that the user actually sees and selects. Next, the With statement retrieves values from the recordset to populate the form's corresponding controls, as shown in Figure C.

Figure C

The form retrieves customer data from the disconnected recordset, not a local table.

At this, point, you can view and modify the data. For example, enter England in the Region field. Doing so doesn't actually change anything as it would if the control were bound. If you closed the form right now, Access would discard the change you made to the Region field.

When you want to save a change, click the Save button. The button's Click event cycles through all the controls, updating the modified values in the disconnected recordset. It's important to remember that the save routine doesn't update a data source.

Notice that the code enables the Update Server button, but don't click it just yet. Instead, close the form. When, you do, the form's Close event destroys the recordset object, dumping any changes you made.

Now, open the form. Doing so creates a new disconnected recordset and retrieves data from the original data source. Access the record for Around the Horn and enter England in the Region control a second time. This time, click Save to update the recordset as you did before. Then, click the enabled Update Server button to send changes to the original data source.

Close the form and open it one last time. Access the record for Around the Horn. This time, as you can see in Figure D, the form retrieved the new Region value, England.

Figure D

Save changes to unbound data.

The example form includes a Save and Update Server button to demonstrate these tasks. As long as the form remains open, the recordset maintains all changes. You can click the Update Server button to send changes as you like. Just remember that if you close the form, you lose unsent changes. Also, the form doesn't warn the user that it will dumping changes. That may or may not be adequate for your needs.

Most likely, you'll want to automate the save and update process a bit more than the example does. The example simply emphasizes that neither the save nor the update actions is automatic. You must plan for both. In addition, you may want to add new records or delete existing ones. To do so, use the AddNew and Delete methods.

If critical data requires immediate updating, disconnected recordsets probably aren't a good idea unless you control the timing programmatically. Furthermore, allowing users to decide when to send changes probably isn't a good idea in any situation.

Disconnect for better performance

Network traffic will definitely affect performance. Retrieving data across a busy network will slow down production and frustrate users. Instead of working, they'll be waiting on slow controls and forms. Disconnected recordsets are much faster and easy to implement. The local database retrieves the data it needs and then disconnects, using the data locally, which is much faster than waiting on a network connection.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
pgrondier
pgrondier

I am very interested in your example, and specifically in the possibilities to populate a combobox from a recordset. I already found a few posts or articles talking about this option, by far more interesting than the standard "rowSource" property of the combobx. Unfortunately, I was not able to obtain similar results with my code: though I have the recordset as requested, and I am able to associate the recordset to the combobox control, I do not have any data in the combobox. PS: I found the trick. The rowSourceType property of the combobox has to be settled to "Table/Query".

Editor's Picks