473,222 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,222 developers and data experts.

Retrieving Data From an ADO Recordset Using GetRows()

ADezii
8,834 Expert 8TB
[OVERVIEW]
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method (GetRows()), but only as it applies to an ADO Recordset. Although there are similarities in the 2 methodologies, the ADO Method offers 2 more Optional Arguments, is a little more complex, and of course, the syntax is different in creating the Recordset. The differences will be noted here along with some similarities, if you wish to see a General Overview of GetRows(), please reference the previous Tip (#49).

[SYNTAX OF ADO GETROWS() VERSION]
  • array = Recordset.GetRows(Rows, Start, Fields)
    • Rows - (Optional), indicates the number of Records to retrieve, and defaults to all Records.
    • Start - (Optional), a String or Variant that evaluates to the Bookmark for the Record from which the GetRows() operation should begin.
    • Fields - (Optional), a Variant that represents a single Field Name, Ordinal Field position, Array of Field Names, or an Array of Ordinal Field positions. Only the data in these Fields are returned by GetRows()
[CODE EXAMPLE]
Expand|Select|Wrap|Line Numbers
  1. Dim rstEmployees As ADODB.Recordset
  2. Dim varEmployees As Variant
  3. Dim intRowNum As Integer
  4. Dim intColNum As Integer
  5.  
  6. 'Make up of qryEmployees (5 Fields/9 Records) based on the
  7. 'sample Northwind.mdb Database
  8.   '[LastName] - Ascending
  9.   '[FirstName] - Ascending
  10.   '[Address]
  11.   '[City]
  12.   '[Region]
  13.  
  14. Set rstEmployees = New ADODB.Recordset
  15.  
  16. With rstEmployees
  17.   .Source = "qryEmployees"
  18.   .ActiveConnection = CurrentProject.Connection
  19.   .CursorType = adOpenKeyset
  20.   .LockType = adLockOptimistic
  21.     .Open
  22.       .MoveLast
  23.       .MoveFirst
  24. End With
  25.  
  26. 'Let's retrieve ALL Rows in the rstEmployees Recordset
  27. varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount)
  28.  
  29. 'Demonstration of the Fields Parameter of GetRows()
  30.  
  31. 'Let's retrieve only the LastName Field in the rstEmployees Recordset
  32. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , "LastName")
  33.  
  34. 'Let's retrieve only the 3rd Field ([Address]) in the rstEmployees Recordset
  35. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , 2)
  36.  
  37. 'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
  38. 'of these Field Names as the Fields Parameter
  39. 'Dim avarFieldNames
  40. 'avarFieldNames = Array("Address", "City", "Region")
  41. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
  42.  
  43. 'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
  44. 'of the Ordinal Positions of these Fields to the Fields Parameter
  45. 'Dim avarFieldNames(1 To 3) As Variant
  46. 'avarFieldNames(1) = 2
  47. 'avarFieldNames(2) = 3
  48. 'avarFieldNames(3) = 4
  49. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
  50. '*****************************************************************
  51. 'Demonstration of the Bookmark Parameter of GetRows()
  52.  
  53. 'Retrieve only those Records from thew Current Bookmark on
  54. 'Dim varBkMrk As Variant
  55. 'rstEmployees.Find "[LastName]='King'"
  56. 'varBkMrk = rstEmployees.Bookmark
  57.  
  58. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, varBkMrk)
  59.  
  60. '*****************************************************************
  61. 'Let's retrieve the first 6 Rows in the rstEmployees Recordset
  62. 'varEmployees = rstEmployees.GetRows(6)
  63.  
  64. 'If fewer than the desired number of Reows were returned
  65. If rstEmployees.RecordCount > UBound(varEmployees, 2) + 1 Then
  66.   MsgBox "Fewer Rows were returned than those requested"
  67. End If
  68.  
  69. '1st Row is the 0 Element of the Array, so we need the +1
  70. '2nd Subscript (2) identifies the Row Number
  71. Debug.Print "Number of Rows Retrieved: " & UBound(varEmployees, 2) + 1
  72.  
  73. Debug.Print
  74.  
  75. '1st Field is the 0 Element of the Array, so we need the +1
  76. '1st Subscript (1) identifies the Field
  77. Debug.Print "Number of Fields Retrieved: " & UBound(varEmployees, 1) + 1
  78.  
  79. Debug.Print
  80.  
  81. 'Let's retrieve the value of the 3rd Field ([Address]) in Row 5
  82. Debug.Print "Field 3 - Row 5: " & varEmployees(2, 4)
  83. 'Let's retrieve the value of the 1st Field ([LastName]) in Row 2
  84. Debug.Print "Field 1 - Row 2: " & varEmployees(0, 1)
  85.  
  86. Debug.Print
  87.  
  88. 'Debug.Print "******************************************"       'Column Format only
  89. Debug.Print "Last Name", "First Name", "Address", , "City", "Region"
  90. Debug.Print "---------------------------------------------------------------------------------------------"
  91. For intRowNum = 0 To UBound(varEmployees, 2)        'Loop thru each Row
  92.   For intColNum = 0 To UBound(varEmployees, 1)      'Loop thru each Column
  93.     'To Print Fields in Column Format with numbered Field and Row
  94.     'Debug.Print "Record#:" & intRowNum + 1 & "/Field#:" & intColNum + 1 & " ==> " & _
  95.                  'varEmployees(intColNum, intRowNum)
  96.     'To Print in Table Format, no numbered Fields or Rows
  97.     Debug.Print varEmployees(intColNum, intRowNum),
  98.   Next
  99.   Debug.Print vbCrLf
  100.   'Debug.Print "******************************************"     'Column Format only
  101. Next
  102.  
  103. rstEmployees.Close
  104. Set rstEmployees = Nothing
[NOTE]
In order to simplify matters for everyone, I've made the sample Database that I used to create both Tips #49 and #50, (DAO and ADO GetRows() Method), available as an Attachment for anyone who is interested to download.
Apr 17 '08 #1
3 43285
sal21
27 16bit
@ADezii
i friend i am very interesting to yuor code...please send me a copy of your project. gss.italyATiol.it
Many tks.
Sal
Jan 31 '09 #2
ADezii
8,834 Expert 8TB
@sal21
There is no Project to send. Everything you need codewise is in Post #9.
Feb 1 '09 #3
sal21
27 16bit
@ADezii
please link of post#9 i dont see that(!?);-(
Feb 1 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: John M | last post by:
Hi, Having struggled to master (or at least get to use) recordsets, and so retrieve data without using a form, I'm stuck at a point which I feel ought to be easy. MsgBox Rs.Myfield (with...
5
by: aniket_sp | last post by:
i am using a data adapter and a dataset for filling and retrieving data into .mdb database. following is the code..... for the form load event Dim dc(0) As DataColumn Try If...
1
by: stjulian | last post by:
If inside a stored procedure, there a SELECT statement to return a recordset and another SELECT to set the value of an output parameter (as in SELECT @OutValue = Name FROM table WHERE pkid=5),...
0
by: Andy | last post by:
Hi All. I'm working for a company that has set out a guideline for retrieving data from a database. Nobody can explain to me the reason for the following. When retrieving a set of records...
3
by: Jakob Petersen | last post by:
Hi, I need to increase the speed when retrieving data from a hosted SQL Server into VBA. I'm using simple SELECT statements. How important is the speed of my Internet connection? (I have...
0
by: DC01 | last post by:
I have added a new measure successfully into the normal cube. I then add it to the virtual cube and reprocess all cubes. I can browse the normal cube successfully. Then when I try and browse the...
4
by: smartin | last post by:
Hi, I'm having problem retrieving data from an SQL stored procedure. I tried debugging but it wont give a the reason for the error. it just throws an exception after executing cmd.ExecuteNonQuery...
9
ADezii
by: ADezii | last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows()...
3
by: Executable | last post by:
Hi everyone, I have a question. I am creating a dynamic report from a table and need to fetch all the data (values and column names) into an array so that I can manipulate the data. I know there...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.