|
|
A field is a column of data containing entries with the same data type. In the ADODB library, the Fields collection used to belong exclusively to recordsets, and its members are Field objects. Field objects have properties and methods for storing and retrieving data. With Access 2002 and Access 2003, the Fields collection also became a member of the Record object. This expands the scope of a field beyond relational database models so that it can also embrace the parent-child model suitable for working with child files as members of parent folders.
Recordsets use a Field object's Value property to display the contents of a column in the current record. Many of the other Field properties contain metadata about the data in a record. The Name property is a handle by which your applications can reference a field. The DefinedSize property characterizes the maximum size of a field (in characters for Text fields). The ActualSize property is the actual length (in bytes) of the contents of a Field object's value. The Attributes property contains an array of information features about a field. It can indicate whether a field's value is updateable or whether it can contain Nulls.
The GetChunk and AppendChunk methods of the Field object facilitate the processing of large text or binary data fields in smaller chunks that fit into memory more conveniently. You use the GetChunk method to bring a portion of a large field into memory. The Size argument specifies the number of bytes to retrieve in one invocation of the GetChunk method. Each uninterrupted, successive invocation of the method starts reading new data from where the previous one finished. The AppendChunk method lets you construct a large text or binary data field in chunks from memory. Like the GetChunk method, AppendChunk writes new data into a field from where the previous AppendChunk method finished. To use either method correctly, a Field object's adFldLong bit in the Attributes property must be set to True.
The following pair of procedures demonstrates an application for the Name and Value properties of the Field object. The application enumerates the field names and values in any row of any table in any database. The first procedure, CallFieldNameValue, passes information that points at a row in a table of a data base. The called procedure, FieldNameValue, constructs a single-record recordset based on the passed arguments. It then enumerates the field names and values for the row in the recordset.
Two alternate sets of passed arguments appear in first procedure. The set without comment markers is for a string criterion, such as the CustomerID field in the Customers table. The set with comments is for a numeric criterion, such as the ShipperID field in the Shippers table. Both sets rely on the Northwind database, but you can freely change all these arguments to specify the field names and values in any particular row of any table within any database. The value for str3 should be the name of a field that has a unique value for each row in the table, such as a primary key. The var1 variable should contain a string value or a number value for the field denoted by str3.
Sub CallFieldNameValue() Dim str1 As String Dim str2 As String Dim str3 As String Dim var1 As Variant 'Specify data source for field name 'and value data str1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= C:\Program Files\Microsoft Office" & _ "\Office11\Samples\Northwind.mdb;" 'Denote a specific record source 'within the data source str2 = "Customers" 'str2 = "Shippers" 'Designate a criterion field (str3) and a criterion 'value (str4) for picking a particular row from 'the record source str3 = "CustomerID" var1 = "BONAP" 'str3 = "ShipperID" 'var1 = 2 'Call the procedure to enumerate field names 'and values FieldNameValue str1, str2, str3, var1 End Sub Sub FieldNameValue(str1 As String, _ str2 As String, str3 As String, _ var1 As Variant) Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset Dim str5 As String Dim fld1 As ADODB.Field 'Open connection and recordset Set cnn1 = New ADODB.Connection cnn1.Open str1 Set rst1 = New ADODB.Recordset rst1.ActiveConnection = cnn1 If IsNumeric(var1) Then str5 = "SELECT * FROM " & str2 & _ " WHERE " & str3 & "=" & var1 Else str5 = "SELECT * FROM " & str2 & _ " WHERE " & str3 & "='" & var1 & "'" End If rst1.Open str5, , , , adCmdText 'Report field names and values for record For Each fld1 In rst1.Fields Debug.Print fld1.Name, fld1.Value Next fld1 End Sub
The second procedure uses a SQL string to designate the source for the single-row recordset. It selects all the rows from any table where the field value in str3 equals the value of the var1 memory variable. After constructing the recordset, a Do loop passes through each field for the first row in the recordset. When you use a primary key value in the WHERE clause of the SELECT statement for the source of the recordset, the first row will be the only row in the recordset. On each iteration, the loop prints the Name and Value properties for one field.
A Field object's Type property indicates the kind of data it can contain. This property returns one of the data type constants in the DataTypeEnum values range. You can view these options in the Object Browser for the ADODB library. Figure 1-8 shows these constants in the Object Browser screen. By selecting the type for a field, you can determine legitimate values for its Value property.
The following two procedures work together to process data type constants with ADO. The FieldNameType procedure opens a recordset based on the Orders table in the Northwind database. This table has a reasonable variety of data types, so it makes a nice case study for examining data types. After opening a recordset, the procedure loops through the fields in the recordset and prints each Field object's name and type. The FieldType function translates the numeric constant's value to a string that represents the constant's name. The adCurrency constant has a value of 6, for example, as shown in Figure 1-8. The FieldType function decodes the value 6 to the string "adCurrency". The FieldNameType procedure then prints each field's name and data type constant name.
Sub FieldNameType() Dim cnn1 As ADODB.Connection Dim rst1 As ADODB.Recordset Dim fld1 As ADODB.Field Dim str1 As String 'Open connection and recordset str1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= C:\Program Files\Microsoft Office" & _ "\Office11\Samples\Northwind.mdb;" Set cnn1 = New ADODB.Connection cnn1.Open str1 Set rst1 = New ADODB.Recordset rst1.ActiveConnection = cnn1 rst1.Open "orders", , , , adCmdTable 'Report field names and types for record For Each fld1 In rst1.Fields Debug.Print " Name: " & fld1.Name & vbCr & _ " Type: " & FieldType(fld1.Type) & vbCr Next fld1 End Sub Public Function FieldType(intType As Integer) As String Select Case intType Case adVarWChar FieldType = "adVarWChar" Case adCurrency FieldType = "adCurrency" Case adInteger FieldType = "adInteger" Case adDate FieldType = "adDate" Case Else FieldType = "Not determined" End Select End Function
You can easily run FieldNameType and FieldType against recordsets based on other data sources than the Orders table, but the FieldType function checks only for the four data types in the Orders table. When you use a record source other than the Orders table, you might encounter a data type other than the four in the list. In this case, the Type field in the report will be "Not determined". You can fix this problem by determining the value of the field. You can do this by putting a breakpoint on the Debug.Print statement inside the Do loop in the FieldNameType procedure. You examine the value of fld1.Type for a field whose type doesn't display and then match that constant value against the constant names in the Object Browser for DataTypeEnum. (See Figure 1-8.) Finally, you amend the Select Case statement in the FieldType procedure to decode the new constant.
| Note |
Place your cursor over an expression or memory variable to determine its value at a breakpoint. You can also track the values of expressions and memory variables in the Watch window. |
Using the Fields collection Append method, you can create fields for a recordset. In addition, your applications can assign values to those fields. Because it is possible to persist recordsets, you can save recordsets that your applications create on the fly to hold data originally stored as a disconnected recordset. You can leave the recordset on the workstation used to create it or transfer it later to a network-based database for sharing by many users.
The following procedure, CreateRecordset, demonstrates the key elements of this approach. First, the procedure instantiates an empty recordset. The procedure uses the Append method to populate the empty recordset with fields. As you can see, the procedure assigns the minimum number of Append arguments. These include the field name and data type. When you specify a string data type, such as adVarWChar, you must also designate its maximum length—ADO terminology calls this the DefinedSize argument. Use the third positional argument to specify a field's DefinedSize. After defining the schema for the recordset, the procedure moves on to populating its two fields with two rows of data. Then it echoes the data to the Immediate window to confirm the data entry operation. Finally, the procedure persists the recordset to a file using the proprietary Microsoft format. So long as the data is exclusively for reading by the recordset's Open method, there is no compelling factor that establishes a preference for either of the two formats available with the Save method.
Sub CreateRecordset()
On Error GoTo CreateRecordset_Trap
Dim rst1 As ADODB.Recordset
Dim strPath As String
Dim strFileName As String
Dim str1 As String
'Instantiate a recordset
Set rst1 = New ADODB.Recordset
'Specify recordset field name and data type.
'Append to recordset object.
rst1.Fields.Append "LastName", adVarWChar, 10
rst1.Fields.Append "ContactID", adInteger
'Add rows to the recordset
rst1.Open
With rst1
rst1.AddNew
rst1("LastName") = "Dobson"
rst1("ContactID") = 9
rst1.Update
rst1.AddNew
rst1("LastName") = "Edelstein"
rst1("ContactID") = 10
rst1.Update
End With
'Echo new contact data to Immediate window
rst1.MoveFirst
Do While Not rst1.EOF
Debug.Print rst1("LastName") & _
" " & rst1("ContactID")
rst1.MoveNext
Loop
'Specify path and file to hold persisted recordset,
'and save recordset to it
strPath = "C:\Access11Files\Chapter01"
strFileName = "NewContactData.adtg"
str1 = strPath + strFileName
rst1.Save str1, adPersistXML
CreateRecordset_Exit:
rst1.Close
Set rst1 = Nothing
Exit Sub
CreateRecordset_Trap:
'Kill previous version of file to hold
'persisted recordset if it exists already
'Otherwise, present error info in a message box.
If err.Number = 58 Then
Kill str1
Resume
Else
MsgBox "Procedure failed with an error number = " _
& err.Number & ", " & vbCrLf & "and an " & _
"error description of """ & _
err.Description & """" & ".", vbInformation, _
"Programming Microsoft Access 2003"
Resume CreateRecordset_Exit
End If
End Sub
The concluding sample for this section illustrates how to perform a common task that builds on and extends several techniques presented throughout this chapter. The sample prepares a text file with the total extended price for each order from the Access Northwind database. The text file also groups orders by quarter and inserts the total extended price of all orders after the orders for each quarter. This sample illustrates common tasks because it demonstrates techniques for grouping and aggregating. In addition, it builds on techniques presented throughout this chapter because it works with Recordset and Field objects. The sample extends the approaches presented in this chapter because it persists row values via the FileSystemObject instead of the Save method for a Recordset object. The FileSystemObject can persist a file as a series of text lines that are easier for humans to read than the XML output that the Save method generates. This is because XML requires text to be bound by tags that can interrupt the easy reading of the contents within a file.
The sample partially relies on two tables linked to the Northwind database. In addition, the sam