Office
 

Microsoft Access 2010 : DATA ACCESS WITH ADO (part 4) - Working with Data in Recordsets

11/21/2011 10:12:24 AM

5. Working with Data in Recordsets

The primary purpose of the Recordset is to work with the data stored within. There are several methods for working with this data, as we discuss in this section.

5.1. Referring to Fields

The ADO Recordset object cursor points to a Record that contains a collection of Field objects (columns). Those fields can be referred to in a variety of ways. The field can be specified by the field name or ordinal index directly from the Recordset object:

rs("myField")
rs(1)
rs!myField

Or, it could be referred to as a member of the Fields collection:

rs.Fields(1)
rs.Fields("myField")
rs.Fields!myField

5.2. Retrieving Data

Gathering the data values from within the Field is quite easy to do, once the correct Record is selected. To get the data from the Field, simply call the Value property, as shown in the following code:

Public Function RetrievingData() As String

' Define Variables
Dim rs As New ADODB.Recordset
Dim result As String

' Open the Recordset
rs.Open "Contacts", CurrentProject.Connection

' Get the data from the Record
result = rs("ID").Value
result = result & " - " & rs("First Name").Value & " " & rs("Last Name").Value

' Return the data
RetrievingData = result

End Function

5.3. Modifying Data

In ADO, changes are (usually) immediately committed to the Recordset when moving away from the record, depending upon the Recordset lock type, cursor mode, connection mode, and other connections to the Recordset. This is completely opposite of DAO, so be sure not to get the two libraries confused! The Supports method can be used to determine if Update is supported, and if not, the data cannot be modified. The following code illustrates just how easily data can be modified:

Public Sub ModifyData()

' Define Variables
Dim rs As New ADODB.Recordset

' Open the Recordset using adLockOptimistic
rs.Open "Contacts", CurrentProject.Connection, , adLockOptimistic

' Modify the Data
If rs("Last Name").Value = "Lincoln" Then
rs("Last Name").Value = "Washington"

Else
rs("Last Name").Value = "Lincoln"
End If

' Move away from the Record to commit the value
rs.MoveNext

' Clean up
rs.Close
Set rs = Nothing

End Sub


It is important to note that, in this example, it is not required to call the Update method to actually update the data in the data source because it was implicitly called when the Recordset object was destroyed. However, it is a best practice to always explicitly call the Update method when modifying a Record to persist changes to the Recordset. Also, the Close method should always be called after completing all tasks on a Recordset to persist the changes and ensure that the data source objects have been released.

5.4. Creating Batch Updates

ADO allows multiple records to be edited and then committed as a single operation, by calling the UpdateBatch method. To use this feature, the cursor type must be client-side and the lock type must be adLockBatchOptimistic.

Public Sub ModifyDataBatch(CommitChanges As Boolean)

' Define Variables
Dim rs As New ADODB.Recordset

' Open the Recordset using adLockBatchOptimistic
rs.Open "Contacts", CreateConnection, , adLockBatchOptimistic

' Modify the Data as a Batch Operation
While rs.EOF = False
If rs("Company").Value = "ABC Corp" Then
rs("Company").Value = "XYZ Inc"
Else
rs("Company").Value = "ABC Corp"
End If

' Move to the Next Record without commiting changes
rs.MoveNext
Wend

' Commit or Deny the changes as a Batch update
If CommitChanges Then
rs.UpdateBatch
Else

rs.CancelBatch
End If

' Clean up
rs.Close
Set rs = Nothing

End Sub


A benefit to using batch updating is that you can also reverse any changes made to the Recordset by calling CancelUpdate. Always call the Close method to persist all updates to the Recordset and ensure that the data source objects have been released.

5.5. Adding New Data

To add a new Record to a Recordset, the Recordset must support the AddNew method, which can be verified with the Supports method. To add a new Record, simply call the AddNew method with an array of Field names and an array of values for each corresponding field.

Public Sub AddNewRecord()

' Define Variables
Dim rs As New ADODB.Recordset
Dim fieldsArray(1) As Variant
Dim valuesArray(1) As Variant

' Open the Recordset using adLockOptimistic
rs.Open "Contacts", CreateConnection, , adLockOptimistic

' Add a new record
fieldsArray(0) = "First Name"
fieldsArray(1) = "Last Name"
valuesArray(0) = "John"
valuesArray(1) = "Adams"
rs.AddNew fieldsArray, valuesArray

' Commit the Record and clean up
rs.Update
rs.Close
Set rs = Nothing

End Sub

Always call the Close method to persist all updates to the Recordset and ensure that the data source objects have been released.
5.6. Deleting Data

To delete a Record from a Recordset, the Recordset must support the Delete method, which can be verified with the Supports method. To delete the Record, simply navigate to it and call the Delete method:

Public Sub DeleteRecord()

' Define Variables
Dim rs As New ADODB.Recordset

' Open the Recordset using adLockOptimistic
rs.Open "Contacts", CreateConnection, , adLockOptimistic

' Move to the last record and delete it
rs.MoveLast
rs.Delete

' Commit the Record and clean up
rs.Update
rs.Close
Set rs = Nothing

End Sub

Always call the Close method to persist all updates to the Recordset and ensure that the data source objects have been released.
5.7. Closing Recordsets

The Close method of the Recordset object is used to close a recordset once you have finished working with its data. It is always a best practice to close Recordsets once you have finished with them to ensure all changes are persisted to the data source. Also, Update, UpdateBatch, Cancel, or CancelBatch should be called before closing to ensure all changes to the Recordset have been committed or rolled back. If all changes are not committed and the Recordset is closed, an error occurs. The following code illustrates an example of a function to close Recordsets:

Public Sub CloseRecordset(rs As ADODB.Recordset)

' Check the Mode to call update
If rs.LockType = adLockBatchOptimistic Then
rs.UpdateBatch
Else
rs.Update
End If

' Close and clean up

rs.Close
Set rs = Nothing

End Sub

5.8. Saving Recordset Data to a File

ADO also provides methods to save a Recordset to a file on the disk. This means that it can later be reopened at any time, and it will not be connected to any other source, such as the database the data came from. To do this, use the Recordset object's Save method to save the Recordset to a file. The PersistFormat parameter specifies which of the two possible save formats to use: Advanced Data TableGram (ADTG), which is a proprietary Microsoft format, or the Extensible Markup Language (XML) format.

Public Sub SaveRecordsetToFile()

' Define Variables
Dim rs As New ADODB.Recordset

' Open the Recordset
rs.Open "Prices", CreateConnection

' Save the Recordset to disk
rs.Save "C:\temp\MyRecordset.xml", adPersistXML

' Clean up
rs.Close
Set rs = Nothing

End Sub

You can continue to work with the Recordset after saving it to disk, but all changes to the Recordset data will be reflected in the database, not in the file. Any data that needs to be saved to a file must be explicitly saved to the file — remember Recordset objects are bound to the database by the connection.
5.9. Loading Recordset Data from a File

The Recordset.Open method can also reload Recordsets that have been saved to a file. To do this, simply call the Open method with the file path to the data file as the parameter.

Public Function LoadRecordsetFromFile() As ADODB.Recordset

' Define Variables
Dim rs As New ADODB.Recordset

' Open the Recordset from File

rs.CursorLocation = adUseClient
rs.Open "C:\temp\MyRecordset.xml"

' Return the Recordset
Set LoadRecordsetFromFile = rs

End Function

Recordset objects opened from a file can be used like any other Recordset, except that these Recordset objects are not connected to any data source, which we will discuss shortly. It is important to note that changes made to this Recordset are not persisted to the original data file implicitly.
5.10. Disconnected Recordsets

When opening a Recordset from a file, the Recordset is put into a disconnected state and it is not connected to any data source; it is known as a disconnected recordset. As mentioned, they can be used like any other Recordset objects, but the Recordset has no ActiveConnection setting value. This means that persisting changes to the data source is different than normal Recordset objects that have a valid ActiveConnection. But, this fact is not to be confused with persisting changes to the data contained in the Recordset object itself. Remember that the data in a disconnected Recordset can be modified in the same way as with any other Recordset object, in that it is just not persisted to any other data source by default. To manually disconnect a Recordset from the data source, simply set the ActiveConnection property to Nothing:

Public Function DisconnectRecordset() As ADODB.Recordset

' Create a new Recordset
Dim rs As New ADODB.Recordset

' Set a Client side cursor required for disconnection
rs.CursorLocation = adUseClient

' Open the Recordset
rs.Open "Prices", CurrentProject.Connection

' Now disconnect the Recordset
Set rs.ActiveConnection = Nothing

' Return the Recordset
Set DisconnectRecordset = rs

End Function

Disconnected Recordset objects have two basic options for persisting data to a data source. The first option is simply saving the Recordset object back to a file, but if the file exists, it should be deleted first; otherwise, the Save method will throw a runtime error. The second method is to actually establish a connection to an ADO data source. This can be either the original data source or a brand new data source. To connect a disconnected Recordset to a data source, simply set the ActiveConnection property to a valid ADO data source.
Public Function ReconnectRecordset() As ADODB.Recordset

' Define Variables
Dim rs As New ADODB.Recordset

' Re-open the Recordset from File
rs.CursorLocation = adUseClient
rs.Open "C:\temp\MyRecordset.xml", , , adLockOptimistic

' Now set the Recordset's ActiveConnection to reconnect to the source
rs.ActiveConnection = CurrentProject.Connection

' Return the Recordset
Set ReconnectRecordset = rs

End Function

Disconnected Recordsets can be extremely handy when trying to take snapshots of data and storing that data in a location other than the data source.
 
Others
 
- Microsoft Access 2010 : DATA ACCESS WITH ADO (part 3) - Navigating Recordsets
- Microsoft Access 2010 : DATA ACCESS WITH ADO (part 2) - Creating Recordsets
- Microsoft Access 2010 : DATA ACCESS WITH ADO (part 1) - Using the Execute Method
- Microsoft Word 2010 : Adding Supplementary Elements - Creating a Bibliography
- Microsoft Word 2010 : Adding Supplementary Elements - Figure Captions & Adding a Table of Figures
- Microsoft Visio 2010 : Tips for Creating Organizational Charts
- Microsoft Visio 2010 : Tips for Creating Timelines
- Microsoft PowerPoint 2010 : Prepare for Delivery - Rehearsing Presentations
- Microsoft PowerPoint 2010 : Prepare for Delivery - Adapting Presentations for Different Audiences
- Microsoft Excel 2010 : Analyzing Worksheet Data - Creating Groups and Outlines, Converting Text to Columns
- Microsoft Excel 2010 : Analyzing Worksheet Data - Charting a PivotTable
- Microsoft Outlook 2010 : Managing a Calendar - Setting Up a Meeting
- Microsoft Outlook 2010 : Managing a Calendar - Adding an Event
- Customizing Microsoft OneNote 2010 : Setting Preferences for Editing and Searching (part 2)
- Customizing Microsoft OneNote 2010 : Setting Preferences for Editing and Searching (part 1)
- Microsoft Project 2010 : Managing Multiple Projects (part 2) - Linking Tasks in Different Projects
- Microsoft Project 2010 : Managing Multiple Projects (part 1) - Creating a Master Project
- Microsoft Project 2010 : Using Sorts and Auto-filters
- Microsoft Access 2010 : Enhancing Reports with VBA - WORKING WITH VBA IN REPORTS
- Microsoft Access 2010 : Enhancing Reports with VBA - INTRODUCTION TO REPORTS
 
 
Most View
 
- SQL Server 2012 : Delivering A SQL Server Health Check (part 12)
- Business Cases for Lync Server 2013 : Why Unified Communications (part 1)
- SQL Server 2012 : Query Optimization (part 1) - Parallel Plans, Algebrizer Trees
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 1) - Choosing a Synchronization Method
- Installing Exchange 2013 : Types of Active Directory deployment that support Exchange
- Sharepoint 2013 : Building an Application with Access Services (part 3) - Creating the Basic Application
- Active Directory Planning and Installation : Installing Active Directory - Promoting a Domain Controller
- Configuring SQL Server 2012 : Setting the Options (part 3) - Configuring the Connection, Surface Area Configuration Facets
- Microsoft Exchange Server 2013 : Accessing and using Exchange Admin Center (part 3) - Working with Exchange Server certificates
- Microsoft Access 2010 : Creating Your Own Databases and Tables - Working with Field Properties (part 5) - The Lookup Wizard
 
 
Top 10
 
- Sharepoint 2013 : Developing Integrated Apps for Office and Sharepoint Solutions - The New App Model for Office
- Overview of Oauth in Sharepoint 2013 : Application Authorization - On-Premises App Authentication with S2S
- Overview of Oauth in Sharepoint 2013 : Application Authorization - Requesting Permissions Dynamically
- Microsoft Excel 2010 : Working with Graphics - Inserting a Diagram,Inserting an Object
- Microsoft Excel 2010 : Working with Graphics - Inserting WordArt, Using Smart Art in Excel
- Microsoft Excel 2010 : Working with Graphics - Using AutoShapes
- Overview of Oauth in Sharepoint 2013 : Application Authentication (part 2) - Managing Tokens in Your Application
- Overview of Oauth in Sharepoint 2013 : Application Authentication (part 1) - Using TokenHelper
- Overview of Oauth in Sharepoint 2013 : Creating and Managing Application Identities
- Overview of Oauth in Sharepoint 2013 : Introduction to OAuth