Every open Recordset must be associated with a data cursor. Behind every
Recordset cursor, there is, among other things, a buffer representing the values of fields in
the current record that the cursor points to. The Fields collection of the Recordset exposes
this record buffer.
You can always programmatically read the values of individual fields in the
record buffer. Depending on whether the current Recordset's cursor type permits writes to the
data, you can also assign values to the record buffer's fields.
There are several syntactic styles for referring to an individual field in the
current Record, as follows:
-
You can use the numeric index of the field in the Fields collection (Fields is zero-based,
so the first field is element 0 in the collection):
rs.Fields(2).Value
This technique is flexible (you could use a numeric variable as the index for the
Fields collection), but it's not quite as useful as the following technique, because
unless you know the position of fields in the data, you will have a hard time getting
the right index.
-
Because the Fields collection also supports index key strings, you can use the field's
name in a string literal or variable to refer to the field in the Fields collection:
rs.Fields("LastName").Value
rs("LastName").Value
Notice the second alternative form of this example, which bypasses an explicit reference
to the Fields collection.
This technique is perhaps the most useful from a programming standpoint, but it also
requires the most runtime overhead and so is the slowest of the three techniques.
-
You can also refer to the field as a temporary property of the Recordset, using the bang
(!) syntax:
rs!LastName
This technique is the most efficient, but it's the least flexible of the three (because
you hard-code the field name in the program, whereas you could substitute variables for
the index and key values in the first and second techniques).
As already mentioned, you can both read and write these fields programmatically.
However, no changes are transferred to the underlying data until you call the Update method as discussed
in the following section.
Unless you need flexibility at runtime, the last method listed is probably
the best to use; it's fast and explicitly identifies the field you want to access. If you want to
read the contents of the current record's Last Name field into the Text property of txtLastName,
for example the line would look like this:
txtLastName.Text = rsEmployees![Last Name] & ""
The Text property of a TextBox control does not accept null data. The use of
the final characters & "" at the end of the line ensures that, even if the
underlying field contains null data, an error will not occur. The & "" makes
sure that at least a blank string is contained in the data being written to the TextBox.
NOTE - Square Brackets Around Field Names: Some
DBMSs support spaces in their field names. MS Access supports spaces in field
names, for example, but SQL Server does not. In Access and SQL Server 7.0, a field
named "Last Name" would be acceptable, but in SQL Server 6.5 and before, it would
not be acceptable. You must place the square bracket characters "[ ]" in your
code around field names that have spaces, as in these examples:
Rs.fields("[Last Name]")
Rs![Last Name]
For consistency you can place square brackets around field names that don't contain
spaces as well, but there is no need to do so.
