Chapter 29

Manipulating the Data in an Access Database via VBA

This chapter shows you how to begin manipulating the data in an Access database. You can do so either from within Access or from another VBA-enabled application—for example, from Excel or from Word. This chapter shows you how to work from within Access.

There are two main ways to manage data in an Access database: via Data Access Objects (DAO) or via ActiveX Data Objects (ADO). DAO is the older technology to access data, and it works for both Microsoft Jet databases (Microsoft Jet is the Access database engine) and ODBC- compliant data sources. (ODBC is Open Database Connectivity, a long-existing standard for accessing databases. ODBC is also useful for accessing open-source solutions, such as MySQL.) ADO is a high-level programming interface that can be used with a wide range of data sources.

Access offers you the choice of methods, but you will probably find it easier to use ADO than DAO. Additional information about choosing between these two technologies can be found at the following location:

http://msdn.microsoft.com/en-us/library/aa164825(office.10).aspx

In this chapter you will learn to do the following:

• Open a recordset
• Access a particular record in a recordset
• Search for a record
• Edit a record
• Insert and delete records

Understanding How to Proceed

Once you've chosen between ADO and DAO, you take the following primary steps to manipulate the data in the database from Access:

1. Add a reference to the object library you'll be using.
2. Create a recordset that contains the records with which you want to work.
3. Work with the records in the recordset.
4. Close the recordset.

All the steps work in more or less the same way for ADO and DAO, except that you create the recordset in different ways. The following sections take you through these steps, splitting the path where necessary to cover the differences in ADO and DAO.

Preparing to Manage the Data in a Database

Given that there are two distinct ways to manage data in Access—ADO and DAO—you have to specify which one you're planning to employ. You can think of libraries as collections of prewritten functions.

Why bother fooling around with multiple libraries? The answer is that there can't be a single, massive, all-purpose library because, among other issues, there would be name confusion. Two different functions in two different libraries might well share the same name. But they could perform different tasks or perform the same task differently. It's like having various libraries in a large university. The word positive means entirely different things in the law library than it does in the medical library.

Note that some of the following code examples will work just fine no matter which library you are currently referencing.

However, to ensure consistency and avoid bugs, create a reference to the object library you want to use (ADO or DAO). And in your code you'll specify the appropriate connection to the data source—the Microsoft ActiveX Data Objects Object 6.1 Library for an ADO connection or Microsoft DAO 3.6 Object Library for a DAO connection. (Note that these 6.1 and 3.6 version numbers might not match the versions of these libraries available on your machine. Just choose the latest, highest version number you see.)

Adding a Reference to the Appropriate Object Library

To create a reference to the object library you need, follow these steps:

1. Launch Access.
2. Launch or activate the VBA Editor by pressing Alt+F11.
3. In the VBA Editor, choose Tools References to display the References dialog box.
4. Scroll down the Available References list box to the appropriate object library item, and then select its check box and click OK to close the References dialog box:
   • For an ADO connection, select the check box for the Microsoft ActiveX Data Objects 6.1 Library item.
   • For a Data Access Object, select the check box for Microsoft DAO 3.6 Object Library.

You can't select both libraries at the same time. And if you don't include the correct library, you'll get a compile error when you try to execute one of the objects in that library (such as a DAO.Recordset). The message will refer to this as a “user-defined” object because it can't find the object in the currently referenced libraries—so the Editor thinks it's a new object introduced by you, the programmer, but that you forgot to declare it.

Establishing a Connection to the Database

It's possible to establish connections to databases in a variety of ways, but in this chapter we'll use a simple, direct line of code. In Chapter 28, “Understanding the Access Object Model and Key Objects,” you saw what steps to take to go online and obtain the Northwind.accdb sample database and where to store it on your hard drive so you could experiment with the example code in these final chapters of the book. If you haven't already taken those steps, see the sidebar in Chapter 28 titled “Prepare the Northwind Database to Use with This Book's Examples.”

To open a connection (but not make it visible to the user in Access) to the Northwind sample database, you can use this code if you're employing DAO:

Dim myDatabase As DAO.Database
Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")

You'll see this approach used in examples later in this chapter. You'll also see how to manipulate Northwind while it's loaded into Access where the user can see it. Recall from the previous chapter that you can open a database two ways: to get to its data but not display it in Access, or to load it into Access and make it visible to the user.

Opening a Recordset

To get to the records in the database to which you're establishing the connection, you must open a recordset. ADO and DAO use different approaches. The following subsections give you the details.

Opening a Recordset Using ADO

To open a recordset using ADO, you use the Open method of the RecordSet object. The syntax for the Open method is as follows:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Here are the components of the syntax:

• recordset is the RecordSet object that you want to open. Often, you'll use an object variable that references the RecordSet object.
• Source is an optional Variant argument that specifies the table, command, SQL statement, or file that contains the recordset.
• ActiveConnection is an optional Variant argument. This can be either an object variable of the Connection type or a Variant/String containing parameters for the connection.
• CursorType is an optional argument for specifying the type of cursor to use in the recordset. Table 29.1 explains the cursor types.
• LockType is an optional argument for specifying how to lock the recordset while it is open. Table 29.2 explains the lock options.
• Options is an optional Long argument that you can use to control how the Source value is evaluated if it is not a Command object. Table 29.3 explains the available constants, which fall into two categories: command-type options and execute options. You can use two or more constants for the Options argument.

You'll see examples of opening a recordset a little later in this chapter. First, you must decide how to access the data in the recordset. The easiest methods are to use an existing table or a SQL SELECT statement.

Choosing How to Access the Data in an ADO Recordset

How you actually get to the data in the recordset you open depends on whether you want to fetch all the data in a table or just part of it. If you want all the data in a table, you can use a table to access the data. If you want to return only particular records, you can use an SQL SELECT statement to fetch them.

USING A TABLE TO ACCESS THE DATA IN AN ADO RECORDSET

To open in a recordset a whole table from a database, specify the table name as the Source argument in the Open statement. The following example declares a RecordSet object variable, uses a Set statement to assign the appropriate recordset type to it, uses the ActiveConnection method to connect to the currently active database (whatever you have loaded into Access at the time), and then uses the Open method to open the entire Customers table. We'll use the Northwind sample database (that you installed in Chapter 28), which has a Customers table.

This example demonstrates how to bring into an ADO recordset the data from an entire table and then move around within this recordset. Your code will not need to instantiate a database object but instead will work with the Northwind database that's currently loaded into Access. (This is a very simple example to illustrate some basic concepts. Normally when accessing a database, you'll want to employ an SQL statement and check for recordset boundary conditions—using BOF and EOF properties. SQL and BOF/EOF are described later in this chapter. For now, just consider the following example code an illustration of elementary principles, to which you'll add real-world maneuvers demonstrated in the code examples later in this chapter.)

As always, it's necessary for you to first ensure that the ADO library is referenced. So in the VBA Editor, choose Tools References and select the check box next to Microsoft ActiveX Data Objects 6.1 Library. Finally, load the Northwind.accdb sample database into Access.

1.   Sub ExploreRecordset()
2.       Dim myRecordset As ADODB.Recordset
3.       Set myRecordset = New ADODB.Recordset
4.
5.  'point to the currently loaded database
6.       myRecordset.ActiveConnection = CurrentProject.Connection
7.       myRecordset.CursorType = adOpenStatic
8.       myRecordset.Open Source:="Customers"
9.
10.  'Display the First Name from the first row
11.      MsgBox myRecordset("First Name")
12.
13.  'Move to the last row and show the Last Name
14.      myRecordset.MoveLast
15.      MsgBox myRecordset("Last Name")
16.
17.  'Move to the previous row and display the Job Title
18.      myRecordset.MovePrevious
19.      MsgBox myRecordset("Job Title")
20.
21.  'Move back to the first row and display the Phone Number
22.      myRecordset.MoveFirst
23.      MsgBox myRecordset("Business Phone")
24.
25.  'Move to the next row and show the Last Name
26.      myRecordset.MoveNext
27.     MsgBox myRecordset("Last Name")
28.
29.
30.      myRecordset.Close
31.      Set myRecordset = Nothing
32.  End Sub

In this code, you first declare a recordset variable, and in line 6 you point it to the database currently loaded in Access. Line 7 defines the cursor type as static, and line 8 loads the data—the entire Customers table—into your recordset.

Line 11 doesn't move anywhere within the recordset, so by merely supplying the recordset's name, MyRecordset, along with one of the table's field names, Last Name, to a MsgBox function, you can display the first record in the table.

Line 14 does move to a different record within the recordset—the last record—before displaying the data in that record's Last Name field. Line 18 moves to the penultimate record, line 22 moves to the first record, and line 26 moves to the second record. Finally, line 30 closes the recordset and line 31 assigns Nothing to the object variable, which has the effect of eliminating it.

myRecordset.Open "Select * from Customers
ORDER BY 'Last Name'
"

myRecordset.Open Source:="Customers"

myRecordset.Open strSQL

USING AN SQL SELECT STATEMENT TO ACCESS A SUBSET OF THE DATA IN AN ADO RECORDSET

If you want to add to your recordset only those records that match criteria you specify, use an SQL SELECT statement. SELECT statements can be constructed in complex ways, but you can also create straightforward statements with a little practice using this syntax:

SELECT [DISTINCT] fields FROM table WHERE criteria ORDER BY fields [DESC]

The words in uppercase are the SQL keywords, and the words in lowercase italics are placeholders for the data you supply, such as the actual name of a real table. Here are the details:

• The SELECT keyword indicates that you're creating a statement to select records (as opposed to, say, delete records).
• You can include the optional DISTINCT keyword (the brackets indicate that it is optional) to make the statement return only unique records, discarding any duplicates that the statement would otherwise return. If you omit DISTINCT, you get any duplicates as well.
• fields is a list of the fields that you want to have appear in the recordset. If you use two or more field names, separate them with commas — for example, contact, company, address. To return all field names, enter an asterisk (*).
• FROM table specifies the name of the table from which to draw the data.
• WHERE criteria specifies the criteria for filtering the records. Enter the field name, an equal sign, a single straight quote, the value you're looking for, and another single straight quote. For example, WHERE City = 'Taos' returns only the results where Taos appears in the City field.
• ORDER BY fields specifies the field or fields on which to sort the results. If you use two or more fields, put them in the order of precedence you want (the first sort field first, the second sort field second, and so on) and separate them with commas. The default sort order is ascending, but you can force a descending sort by adding the DESC keyword. For example, ORDER BY Zip DESC produces a descending sort by the Zip field, while ORDER BY State, City produces an ascending sort by the State field and, within that, by City.

Because SQL SELECT statements contain so many elements, putting a SELECT statement as an argument in an Open statement can create uncomfortably long lines of code. You can break the lines of code in the editor with the underscore symbol as usual, but you may find it easier to use the properties of the RecordSet object to specify the details of the recordset rather than using the Open arguments.

Another way to avoid using a large SQL statement as an argument for the Open method is to first assign the SELECT statement to a String variable and then use that string to supply the argument. The following code illustrates that approach.

In this code, we'll assign an SQL statement to a string and then use that string as the argument for the Open statement. Before executing this example, press Ctrl+G in the VBA Editor to open the Immediate window, where the results will be displayed.

Sub SubSet()

    Dim strSQL As String

    Dim myRecordset As ADODB.Recordset
    Set myRecordset = New ADODB.Recordset
    myRecordset.ActiveConnection = CurrentProject.Connection
    strSQL = "Select * FROM Customers WHERE ID > 17"
    myRecordset.Open strSQL

    Do Until myRecordset.EOF
        Debug.Print myRecordset("Last Name")
        myRecordset.MoveNext
    Loop
End Sub

In this example, you want to import into the recordset only those records that have an ID higher than 17, so you set up an SQL statement that specifies that condition. Then you looped through the recordset until EOF (end of file), displaying each last name in the Immediate window.

Opening a Recordset Using DAO

When working with DAO, you use a different approach than the ADO techniques explored so far in this chapter. You use the OpenRecordset method of the Database object to create a new recordset and add it to the Recordsets collection.

The syntax for the OpenRecordset method is as follows:

Set recordset = object.OpenRecordset (Name, Type, Options, LockEdit)

Here are the components of the syntax:

• recordset is an object variable representing the RecordSet object you're opening.
• object is an object variable representing the database from which to create the new RecordSet object.
• Name is a required String argument that specifies the table, query, or SQL statement that provides the records for the recordset. If you're using a Jet database and returning a table-type recordset, you can use only a table name for the Name argument.
• Type is an optional argument that you can use to specify the type of recordset you're opening. Table 29.4 explains the constants you can use for Type.
• Options is an optional argument that you can use to specify constants that control how Access opens the recordset. Table 29.5 explains the constants you can use for Options.
• LockEdit is an optional constant that you can use to specify how the recordset is locked. Access 2013 no longer supports ODBCDirect workspaces. So if you need to connect to external data stores directly (not through Access's database engine), then you must use ADO rather than DAO. Table 29.6 explains the constants you can use for LockEdit.

OPENING A DAO RECORDSET USING A TABLE

The easiest way to open a DAO recordset is to open an entire table by specifying the table name for the Name argument and using Type: = dbOpenTable to explicitly state that you're opening a table. The following example declares the object variable myRecordset as a DAO. Recordset object and then assigns to it the records from the Customers table in the database identified by the myDatabase object variable:

Sub DAOTest()
Dim myRecordset As DAO.Recordset
Dim myDatabase As DAO.Database

   'Open the copy of Northwind on the hard drive
   Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")

   'Create the DAO-style Recordset

Set myRecordset = myDatabase.OpenRecordset(Name:="Customers", _
    Type:=dbOpenTable)

   MsgBox myRecordset("ID")
   MsgBox myRecordset("Company")
   MsgBox myRecordset("Address")
   MsgBox myRecordset("City")

   Set myRecordset = Nothing
End Sub

OPENING A DAO RECORDSET USING AN SQL SELECT STATEMENT

If you want to return only a subset of records rather than an entire table, use an SQL SELECT statement to open the DAO recordset. (See “Using an SQL SELECT Statement to Access a Subset of the Data in an ADO Recordset,” earlier in this chapter, for an explanation of the essentials of SQL SELECT statements.)

Specify the SQL statement as the Name argument for the OpenRecordset method, as the following example illustrates. This code declares a Database object variable, assigns the Northwind sample database to it, declares a RecordSet object variable, and then assigns to the object variable the results of a SELECT statement run on the database:

Sub DAOSelect()

Dim myDatabase As DAO.Database
   Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")

Dim myRecordset As DAO.Recordset
   Set myRecordset = myDatabase.OpenRecordset _
    (Name:="SELECT * FROM Customers WHERE City ='Boston'", _
    Type:=dbOpenDynaset)
   Do Until myRecordset.EOF
       Debug.Print myRecordset("Last Name")
       myRecordset.MoveNext

   Loop

   Set myRecordset = Nothing
End Sub

Note that the results in this example are printed in the VBA Editor's Immediate window, so press Ctrl+G to open that window before pressing F5 to test this procedure.

Accessing a Particular Record in a Recordset

To work with a particular record in a recordset, you can either move through (loop) the records until you find the one you want or search for the record using Seek or Find methods. The RecordSet object includes these methods for moving about the records in the recordset:

Using the MoveFirst, MoveNext, MovePrevious, and MoveLast Methods

The MoveFirst method and MoveLast method are always safe to use because as long as the recordset contains one or more records, there's always a first record and a last record. (If the recordset contains only one record, that record is considered both first and last.)

But if you use the MovePrevious method from the first record in the recordset or the MoveNext method from the last record, you move beyond the recordset, accessing what is sometimes called a “phantom record”—one that isn't there. When you try to access the contents of such a record, VBA gives the runtime error 3021 ("No current record"). Figure 29.1 shows this error.

FIGURE 29.1 The runtime error “No current record” usually means that you've moved outside the recordset.

BOF means beginning of file, and EOF means end of file. Note that you can visualize the end of a recordset as a point just beyond the last record. EOF, therefore, is not the same as the last record. BOF, likewise, is not the first record, but a point just before it. (I mention this because we have a tendency to view the first item in a set as the “beginning” of the set; we would consider the first float as the beginning of a parade. Recordsets aren't like that.)

To check whether you're at the beginning or end of the recordset, use the BOF property or the EOF property of the RecordSet object. The BOF property returns True when the current record is at the beginning of the file, and the EOF property returns True when the current record is at the end of the file. To avoid errors, after using the MovePrevious method check whether the beginning of the file has been reached, as in this example:

With myRecordset
    .MovePrevious
    If .BOF = True Then .MoveNext
End With

Similarly, after using the MoveNext method check whether the end of the file has been reached:

myRecordset.MoveNext
If myRecordset.EOF Then myRecordset.MovePrevious

Using the Move Method to Move by Multiple Records

To move by several records at once, but not to the first record or last record in the recordset, use the Move method. The syntax for ADO differs from that used with DAO.

Here's the syntax for the Move method with ADO:

recordset.Move NumRecords, Start

The syntax for the Move method with DAO is as follows:

recordset.Move Rows, StartBookmark

Here, recordset is the recordset involved, NumRecords or Rows is the number of records by which to move (use a positive number to move forward or a negative number to move back), and Start or StartBookmark is an optional argument that you can use to specify a bookmark from which you want to start the movement. If you omit Start or StartBookmark, movement starts from the current record.

For example, the following statement moves 10 records forward from the current record in an ADO recordset:

myRecordset.Move NumRecords:=10

The following statement moves 5 records backward from the current record in a DAO recordset:

myRecordset.Move Rows:=−5

To create a bookmark, move to the record that you want to mark, and then use the Bookmark property of the RecordSet object. The following example declares a Variant variable named myBookmark and then assigns to it a bookmark representing the current record in an ADO recordset:

Dim myBookmark As Variant
myBookmark = myRecordset.Bookmark

After setting a bookmark, you can use it as the starting point of a move. For example, the following statement moves to the eighth record after the bookmark myBookmark in an ADO recordset:

myRecordset.Move NumRecords:=8, Start:=myBookmark

Searching for a Record

The process of searching for a record in a recordset differs in ADO and in DAO. The following sections show you how to search using either technology.

Searching for a Record in an ADO Recordset

To search for a record in an ADO recordset, you can use the Find method of the RecordSet object. The syntax is as follows:

recordset.Find Criteria, SkipRows, SearchDirection, Start

Here are the components of the syntax:

• recordset is the recordset involved.
• Criteria is a required String argument that specifies the column name, type of comparison, and value to use. For example, to locate a record where the state is California, you could specify that the State column is equal (=) to CA.
• SkipRows is an optional Long value that you can use to specify an offset from the current row (or from the bookmark specified by the Start argument) at which to start searching instead of starting from the current row. For example, an offset of 3 starts the search three rows later than the current row.
• SearchDirection is an optional argument for specifying whether to search forward or backward. The default is adSearchForward; specify adSearchBackward to search backward instead.
• Start is an optional Variant argument that specifies the bookmark from which to start the search or the offset. If you omit Start, the search starts from the current row.

When you run the search, it stops at the first matching record. If no record matches and you're searching forward, it stops at the end of the recordset; if you're searching backward, it stops at the beginning of the recordset. If the end or beginning of the recordset is reached, you know that there was no match for the search.

The following example begins by moving to the first record in the recordset that is represented by the object variable myRecordset. Then the code searches for the first record that matches the criterion "City = 'Denver'". The example checks the EOF property to ensure that the end of the recordset has not been reached. If it has not, this means we found a record containing Denver in the City field, so the example displays a message box with the last name data for the record matching Denver. However, if the end of the recordset has been reached, the example displays a message box stating that no match was found:

Sub SearchADO()

    Dim strSQL As String

    Dim myRecordset As ADODB.Recordset
    Set myRecordset = New ADODB.Recordset
    myRecordset.ActiveConnection = CurrentProject.Connection

    myRecordset.Open Source:="Select * from Customers", _
        Options:=adCmdText

    With myRecordset
    .MoveFirst
    .Find Criteria:="City='Denver'"

    If Not .EOF Then
        MsgBox .Fields("Last Name")
    Else
        MsgBox "No matching record was found."
    End If

End With

End Sub

To continue your search for the same criteria, you can use the SkipRows argument to specify an offset so that you don't simply find the current record again. For example, you'll likely want to move ahead just one row, like this:

myRecordset.Find Criteria="City='Denver'", SkipRows:=1

Searching for a Record in a DAO Recordset

To search for a record in a DAO recordset, you can use one of these four methods:

• The FindFirst method starts searching at the beginning of the recordset and searches forward.
• The FindNext method starts searching at the current record and searches forward.
• The FindPrevious method starts searching at the current record and searches backward.
• The FindLast method starts searching at the end of the recordset and searches backward.

The syntax for these four methods is as follows:

recordset.FindFirst Criteria
recordset.FindNext Criteria
recordset.FindPrevious Criteria
recordset.FindLast Criteria

Here, recordset is a required object variable that represents the RecordSet object involved. Criteria is a required String argument that specifies the criteria for the search. Criteria works in the same way as the WHERE clause in an SQL statement, except that it does not use the word WHERE.

The following example uses the FindFirst method to search from the beginning of the recordset for the first record that matches the criterion City = 'Las Vegas':

Sub DAOSearch()

Dim myDatabase As DAO.Database
Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")
Dim myRecordset As DAO.Recordset
Set myRecordset = myDatabase.OpenRecordset _
     (Name:="SELECT * FROM Customers", _
     Type:=dbOpenDynaset)

myRecordset.FindFirst "City = 'Las Vegas'"

MsgBox myRecordset("Last Name")

   Set myRecordset = Nothing
End Sub

When you start a search in a DAO recordset using one of the four Find methods, the NoMatch property of the RecordSet object is set to True. If the method finds a match, the NoMatch property is set to False. So you can test the NoMatch property to tell whether or not the search found a match, as in this example:

If myRecordset.NoMatch = False Then
   MsgBox myRecordset("Last Name")
End If

Returning the Fields in a Record

Once you've moved to a record, you can return the fields it contains by using the appropriate Field object from the Fields collection. Field is the default property for the RecordSet object, so you can omit it if you choose. For example, both the following statements return the Last Name field from the current record:

myRecordset.Fields("Last Name")
myRecordset("Last Name")

Editing a Record

To change the data in a record, first use the Edit method to specify the value you want to store in the field, and then use the Update method of the RecordSet object to update the data in the underlying table. The following example prepares a record for editing with the Edit method, changes the value in the Last Name field to Schmidtz, and then uses the Update method to update it:

With myRecordset
     .Edit
     .Fields("Last Name").Value = "Schmidtz"
     .Update
End With

Inserting and Deleting Records

To insert a new record, use the AddNew method of the RecordSet object. You can then assign data to the fields in the record. After that, use the Update method to save the data to the table in the database. The following example uses a With statement to perform these actions:

Sub AddOne()

Dim myDatabase As DAO.Database
Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")
Dim myRecordset As DAO.Recordset
Set myRecordset = myDatabase.OpenRecordset _
    (Name:="SELECT * FROM Customers", _
    Type:=dbOpenDynaset)

With myRecordset
    .AddNew
    .Fields("ID").Value = 32
    .Fields("Last Name").Value = "Murphy"
    .Fields("First Name").Value = "Andrea"
    .Fields("Company").Value = "Company RP"
    .Fields("City").Value = "City of Industry"
    'add data for the other fields here
    .Update
End With

   Set myRecordset = Nothing

End Sub

After you press F5 in the VBA Editor to test this code, switch to Access, display the Customers table, and then you will need to press F5 to refresh the view in Access before you can see this new record.

To delete a record, identify it by either moving to it or searching for it, and then use the Delete method followed by the Update method. The following example deletes the current record and then updates the table:

myRecordset.Edit
myRecordset.Delete
myRecordset.Update

Closing a Recordset

After working with an object, you should close it. To close a recordset, use the Close method with the appropriate RecordSet object or the object variable that represents the RecordSet object. The following example closes the recordset represented by the object variable myRecordset:

myRecordset.Close

After closing the recordset, set its object variable to Nothing to release the memory it occupied:

Set myRecordset = Nothing

Saving a Recordset to the Cloud

You might want to store a recordset on your hard drive or in the cloud. As you've seen in cloud-access examples in previous chapters, saving files to the cloud is much the same as saving to an ordinary hard-drive folder. By the way, this example also illustrates how to use the Save method of the RecordSet object:

1. Sub SaveToCloud()
2.
3.     Dim myRecordset As ADODB.Recordset
4.     Set myRecordset = New ADODB.Recordset
5.     myRecordset.ActiveConnection = CurrentProject.Connection
6.
7.     Dim strSQL As String
8.     Dim strFilepath As String
9.     strFilepath = "C:UsersRichardSkyDriveCities.xml"
10.
11.        strSQL = "SELECT city FROM Employees"
12.        myRecordset.Open strSQL
13.
14.        myRecordset.Save strFilepath, adPersistXML
15.
16.    Set myRecordset = Nothing
17.
18. End Sub

To test this, open Northwind and press Alt+F11 to open the VBA Editor. Paste this code into a module, but change Richard in line 9 to your own name.

Most of this code should be understandable from previous examples in this chapter. Line 9 specifies the location on my hard drive where files move to SkyDrive automatically after being saved there. You could just as easily save this recordset to any ordinary hard-drive folder, like this:

myRecordset.Save "c:	empCities.xml", adPersistXML

The save command we're using stores this recordset in the XML format, about which I'll have much more to say in Chapter 31, “Programming the Office 2010 Ribbon.”

However, for the curious, here's what this recordset looks like in the XML format, showing the city data for the nine records in the Employees table:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
      xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
      xmlns:rs='urn:schemas-microsoft-com:rowset'
      xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
      <s:ElementType name='row' content='eltOnly'>
            <s:AttributeType name='city' rs:number='1' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
                  <s:datatype dt:type='string' dt:maxLength='50'/>
           </s:AttributeType>
           <s:extends type='rs:rowbase'/>
     </s:ElementType>
</s:Schema>
<rs:data>
      <z:row city='Seattle'/>
      <z:row city='Bellevue'/>
      <z:row city='Redmond'/>
      <z:row city='Kirkland'/>
      <z:row city='Seattle'/>
      <z:row city='Redmond'/>
      <z:row city='Seattle'/>
      <z:row city='Redmond'/>
      <z:row city='Seattle'/>
</rs:data>
</xml>

The Bottom Line

Open a recordset. You can open an ADO recordset in two different ways.

Master It One way to open an ADO recordset is to provide an argument list following the Open method. What is the other way to open an ADO recordset, which doesn't involve using arguments? Some people say that this second approach makes their code easier to read.

Access a particular record in a recordset. Both ADO and DAO technologies have methods that allow you to move around within a recordset.

Master It One method you can use to traverse a recordset is the MoveFirst method. It takes you to the first record in the recordset. What does the first record mean in a recordset in a relational database? Is it the record that's the lowest numerically, the lowest alphabetically, or what?

Search for a record. Both ADO and DAO offer methods to directly search for a particular record.

Master It ADO offers a Find method. How many methods does DAO offer, and what are they?

Edit a record. When editing a record, you first use the Edit method, and then you can change the value in a field.

Master It After you have made a change to a value in a record, what method do you use to save this change to make it part of the database?

Insert and delete records. It's not difficult to insert new records or delete existing ones. In both situations, you use the Update method when finished to save the changes to the database.

Master It To insert a new record into a recordset, what method do you use before you can assign data to the fields in the new record?