| It's when you begin using recordsets that you start making significant claims on your system's resources. As Chapter 9 mentioned, SQL queries both are powerful and make relatively efficient use of processors and memory. Whenever you reasonably can use a SQL query to append data, or to update data, or to select and display data, by all means do so. But queries are bludgeons. They don't do well in situations that call for branching and looping, for example. The more complicated the logic that you must bring to bear on the data, the less attractive a SQL query looks. Recordsets, on the other hand, enable you to use more delicate tools. You can use the extensive capabilities of VBA when you work with recordsets and that includes the rich set of worksheet functions that accompanies Excel. For example, if you wanted to add records to a table depending on where they fall relative to some median value, you'd almost certainly opt for VBA in conjunction with a recordset, rather than a SQL query. SQL doesn't typically offer a function that returns a median. (It's not hard to write your own, but why bother when it's already available?) A recordset is analogous to an Excel list or a database table. It's also analogous to the array named FieldArray that was used in the section named "Creating a New Table in the Database with DAO": an array of records with elements that can have different types. A recordset consists of one or more records and one or more fields. You declare a recordset in VBA after you've established a reference to a DAO or an ADO library. Then you can declare that a recordset exists, give it a name, and assign it to a database table or query. After the recordset has been established, you use VBA commands to manipulate its data by adding to it, deleting from it, modifying the values in its fields, importing its records into another location such as an Excel worksheet or a Word document, and so on. As an introduction to recordsets, consider the following code, which establishes a recordset using DAO. You might use it in conjunction with the code discussed in the prior section that created a new database, a table to hold records, and fields in that table to contain the actual values. A good place to put it would be at the end of the Driver subroutine, in the final If block: If GoAhead Then MakeNewTableWithDAO DatabaseName, TableName, FieldName() AddRecordsWithDAO DatabaseName, TableName, FieldCount End If
Here's the code. It establishes a recordset based on the new database table and adds records to it. The records are taken from the worksheet. Sub AddRecordsWithDAO(DatabaseName As String, _ TableName As String, FieldCount As Integer) Dim i As Integer, j As Integer Dim RecordCount As Long, LastRowInColumn As Long Dim dbDataFile As DAO.Database Dim rsDataRecords As DAO.Recordset
After declaring some variables, the code goes on to set the database using the DatabaseName variable (recall that DatabaseName includes the path to the database). Then the recordset named rsDataRecords is established. Set dbDataFile = OpenDatabase(DatabaseName) Set rsDataRecords = dbDataFile.OpenRecordset(TableName, _ dbOpenTable)
The assignment of the recordset needs a little explanation. The object variable rsDataRecords is assigned to the result of the OpenRecordset method. That method as used here takes two arguments: Source The source of the recordset is the name of a table or query in which the records are found, or into which the records will be placed. The source can also be a SQL query in text form, although this usage is rare in practice. In the current example, the recordset's source is the value of TableName. Type In DAO, there are various types of recordsets, such as dynaset, table type, and snapshot. The different types and the implication of choosing a particular type are discussed later in this chapter, in "Understanding DAO Recordset Types." The current example specifies a table type.
For i = 1 To FieldCount LastRowInColumn = ActiveSheet.Cells(65536, 1). _ End(xlUp).Row If RecordCount < LastRowInColumn Then RecordCount = LastRowInColumn End If Next i
After the recordset has been established, the code determines the number of records to be taken from the worksheet and placed into the database table. It does this by looping through the number of columns (determined earlier, in the Driver subroutine) and finding the final used cell in each of them. The RecordCount variable is used to determine the largest number of records in any column. The heart of the subroutine is in the following nine statements. They're enclosed in a With block, which takes the recordset as its object. With rsDataRecords
By using the With block, the code avoids repetitively naming the recordset and causing the code to navigate through the recordset to its methods and properties. The code then enters a nested loop. The outer loop cycles through the records. Notice that it begins at 2, not 1, because on the worksheet the records begin in row 2, using row 1 for the headers. For j = 2 To RecordCount
The first statement in the outer loop adds a new record to the recordset. One of the effects of adding a new record is to make it the current record; any record operations that take place do so on the current record, until another one becomes current. Notice the use of the dot before the AddNew keyword. The dot means that AddNew belongs to the With statement's object; in this case, the recordset. The code is adding a new record to the recordset. .AddNew
With a new record current, and at the outset empty, the inner loop runs. It cycles through the fields in the recordset. Its purpose is to put the value of each column in the worksheet's current row into the corresponding field in the current record. While the inner loop is executing, the code remains on the same worksheet row and puts a value in a field in the same record. For i = 1 To FieldCount .Fields(i - 1) = ActiveSheet.Cells(j, i) Next i
There are three items of note about this inner loop. First, the counter j remains constant. There's nothing in the loop that changes it, and therefore the Cells reference points to the same row as long as the loop executes. It's the outer loop, the one that cycles through the list's rows, that increments j. Second, notice the use of the dot before the Fields keyword. This means that the fields belong to the object named in the With again, that's the recordset. In the example, just like the AddNew method, the Fields collection belongs to rsDataRecords. Also notice that the recordset's fields are indexed by i 1 instead of by i. The first field in a recordset is field number 0, the second field is field number 1, and so on. This is a little disconcerting until you get used to it. (Option Base 1 has no effect on field indexes, by the way, just on memory arrays.) But you're likely to get used to it fairly quickly because by far the most common use of loops and field indexes in VBA is to move data back and forth between recordsets and worksheets and worksheets have no column numbered zero. .Update
When you add a new record, as here, you edit it. In this example, the editing process takes place in the inner loop, where contents of the worksheet cells are placed in the active record of the recordset. The editing doesn't take place on the recordset itself, but in a copy buffer. That is, as the code picks up values from the worksheet and apparently places them in fields in the recordset, it actually and temporarily places the values in the copy buffer, a memory location that Access manages. It isn't until the code executes an Update statement that the values are moved from the copy buffer and placed in the recordset. Anytime your code uses a statement with AddNew (or, if you're editing a record that already exists, a statement with Edit) you have to follow it with an Update. Otherwise the values in the copy buffer are lost. NOTE The Update requirement is true only of code using DAO. An Update statement is not required if you're using ADO. But see Chapter 12, in the section titled "Using ADO to Add Records," for reasons that you should use it anyway.
Next j End With rsDataRecords.Close dbDataFile.Close End Sub
Here's the full code for AddRecordsWithDAO: Sub AddRecordsWithDAO(DatabaseName As String, _ TableName As String, FieldCount As Integer) Dim i As Integer, j As Integer Dim RecordCount As Long, LastRowInColumn As Long Dim dbDataFile As DAO.Database Dim rsDataRecords As DAO.Recordset Set dbDataFile = OpenDatabase(DatabaseName) Set rsDataRecords = dbDataFile.OpenRecordset(TableName, dbOpenTable) For i = 1 To FieldCount LastRowInColumn = ActiveSheet.Cells(65536, 1).End(xlUp).Row If RecordCount < LastRowInColumn Then RecordCount = LastRowInColumn End If Next i With rsDataRecords For j = 2 To RecordCount .AddNew For i = 1 To FieldCount .Fields(i - 1) = ActiveSheet.Cells(j, i) Next i .Update Next j End With rsDataRecords.Close dbDataFile.Close End Sub
Figure 10.5 shows the appearance of the Admits table in datasheet view after this code has put the records in the worksheet into the table. Figure 10.5. The times shown on the worksheet have been saved as date/time serial numbers. 
The times from the worksheet have been saved in an inconvenient format. Perhaps the most straightforward way of handling this is after the fact. As noted earlier in this chapter, it would be possible to determine data types by writing code to examine the worksheet data, but if you don't do that, you can convert the data using an update query. Figure 10.6 shows the query in design view. Figure 10.6. The CDate function in Access converts a serial number to a date/time representation. 
As shown in Figure 10.6, two new fields have been added to the Admits table: ConvertedAdmitTime and ConvertedDischargeTime. The update query uses the CDate function to convert the times, typed as String, from the worksheet to times, typed as Date/Time, in the database. The result of running the query is shown in Figure 10.7. Figure 10.7. The fractional portion of a serial number specifies the time of day; the integer portion specifies a date. 
Notice that the converted times shown in Figure 10.7 match the original times as shown on the worksheet in Figure 10.1. | 