The Field Object


The Field Object

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 .

Note  

The DefinedSize and ActualSize properties use different measurements for Text fields. DefinedSize is the maximum number of characters in the field, and ActualSize is the number of bytes in the field. Since a Text field with Jet 4 represents characters with 2 bytes each, its ActualSize value can be up to twice the DefinedSize value. For numeric fields, and Text fields in databases that represent characters using a single byte (for example, a Jet 3.51 database), this difference does not exist. If you are migrating from Access 97 to Access 2003, you are probably using Jet 3.51. Therefore, you should be especially sensitive to this distinction.

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 .

Name and Value Properties

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 .

 SubCallFieldNameValue() Dimstr1AsString Dimstr2AsString Dimstr3AsString Dimvar1AsVariant     'Specifydatasourceforfieldname 'andvaluedata str1="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" 'Denoteaspecificrecordsource 'withinthedatasource str2="Customers" 'str2="Shippers"     'Designateacriterionfield(str3)andacriterion 'value(str4)forpickingaparticularrowfrom 'therecordsource str3="CustomerID" var1="BONAP" 'str3="ShipperID" 'var1=2     'Calltheproceduretoenumeratefieldnames 'andvalues FieldNameValuestr1,str2,str3,var1     EndSub     SubFieldNameValue(str1AsString,_ str2AsString,str3AsString,_ var1AsVariant) Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimstr5AsString Dimfld1AsADODB.Field     'Openconnectionandrecordset Setcnn1=NewADODB.Connection cnn1.Openstr1 Setrst1=NewADODB.Recordset rst1.ActiveConnection=cnn1 IfIsNumeric(var1)Then str5="SELECT*FROM"&str2&_ "WHERE"&str3&"="&var1 Else str5="SELECT*FROM"&str2&_ "WHERE"&str3&"='"&var1&"'"     EndIf rst1.Openstr5,,,,adCmdText     'Reportfieldnamesandvaluesforrecord ForEachfld1Inrst1.Fields Debug.Printfld1.Name,fld1.Value Nextfld1     EndSub 

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.

The Type Property

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.

click to expand
Figure 1.8: The Object Browser showing a selection of data type constants for defining fields.

Printing Field Data Types

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.

 SubFieldNameType() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimfld1AsADODB.Field Dimstr1AsString 'Openconnectionandrecordset str1="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice"&_ "\Office11\Samples\Northwind.mdb;" Setcnn1=NewADODB.Connection cnn1.Openstr1 Setrst1=NewADODB.Recordset rst1.ActiveConnection=cnn1 rst1.Open"orders",,,,adCmdTable 'Reportfieldnamesandtypesforrecord ForEachfld1Inrst1.Fields Debug.Print"Name:"&fld1.Name&vbCr&_ "Type:"&FieldType(fld1.Type)&vbCr Nextfld1 EndSub     PublicFunctionFieldType(intTypeAsInteger)AsString SelectCaseintType CaseadVarWChar FieldType="adVarWChar" CaseadCurrency FieldType="adCurrency" CaseadInteger FieldType="adInteger" CaseadDate FieldType="adDate" CaseElse FieldType="Notdetermined" EndSelect EndFunction 

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.

Creating Fields for a Recordset

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.

 SubCreateRecordset() OnErrorGoToCreateRecordset_Trap Dimrst1AsADODB.Recordset DimstrPathAsString DimstrFileNameAsString Dimstr1AsString     'Instantiatearecordset Setrst1=NewADODB.Recordset     'Specifyrecordsetfieldnameanddatatype. 'Appendtorecordsetobject. rst1.Fields.Append"LastName",adVarWChar,10 rst1.Fields.Append"ContactID",adInteger     'Addrowstotherecordset rst1.Open Withrst1 rst1.AddNew rst1("LastName")="Dobson" rst1("ContactID")=9 rst1.Update rst1.AddNew rst1("LastName")="Edelstein" rst1("ContactID")=10 rst1.Update EndWith     'EchonewcontactdatatoImmediatewindow rst1.MoveFirst DoWhileNotrst1.EOF Debug.Printrst1("LastName")&_ ""&rst1("ContactID") rst1.MoveNext Loop     'Specifypathandfiletoholdpersistedrecordset, 'andsaverecordsettoit strPath="C:\Access11Files\Chapter01" strFileName="NewContactData.adtg" str1=strPath+strFileName rst1.Savestr1,adPersistXML     CreateRecordset_Exit: rst1.Close Setrst1=Nothing ExitSub     CreateRecordset_Trap: 'Killpreviousversionoffiletohold 'persistedrecordsetifitexistsalready 'Otherwise,presenterrorinfoinamessagebox. Iferr.Number=58Then Killstr1 Resume Else MsgBox"Procedurefailedwithanerrornumber="_ &err.Number&","&vbCrLf&"andan"&_ "errordescriptionof"""&_ err.Description&""""&".",vbInformation,_ "ProgrammingMicrosoftAccess2003" ResumeCreateRecordset_Exit EndIf     EndSub 

Persisting Quarterly Totals to a Text File

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



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net