HOWTO: Use a Fields Array in the Fields Option of GetRows Method

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2

SUMMARY

The ActiveX Data Objects (ADO) reference for the GetRows method states that it is possible to provide an optional Fields array in the Fields Option to limit the columns returned by GetRows to one or more specified columns. This article demonstrates how to use this option because there is no example given in the ADO Help file.

MORE INFORMATION

The GetRows method has the following arguments:

• Rows: Optional. A Long expression indicating the number of records to retrieve. Default is adGetRowsRest (-1).



• Start: Optional. A String or Variant that evaluates to the bookmark for the record from which the GetRows operation should begin. One of the BookmarkEnum constants can be used for this argument.



• Fields: Optional. A Variant representing a single field name or ordinal position or an array of field names or ordinal position numbers. ADO returns only the data in these fields.

• vdata = oRs.GetRows(adGetRowsRest, adBookmarkCurrent, vFields)

Steps To Accomplish Task

1. Create a Standard .EXE project in Visual Basic.



2. Add a reference to the Microsoft ActiveX Data Objects Library.



3. Add the following sample code to the default form's Load method:




   'This sample code demonstrates how to pass an array of field
   'names to the GetRows Fields Option.

   Dim cn As New Connection
   Dim rs As New Recordset
   Dim vFields() As Variant
   Dim vdata as Variant

   'This example is connecting to SQL Server's Sample Pubs database.
   cn.Open "driver={SQL Server};" & _
      "server=<server_name>;uid=<user_id>;pwd=<password>;database=pubs"
   rs.Open "select * from authors", cn
   ReDim vFields(1)
   vFields(0) = "au_fname"
   vFields(1) = "au_lname"
   vData = rs.GetRows(adGetRowsRest, , vFields)

   For i = 0 To UBound(vdata, 2) - 1
   Debug.Print vdata(0, i) & " " & vdata(1, i)
   Next i 

Additional query words:

Keywords : kbADO kbADO200 kbDatabase kbSQLServ kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC200 kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto