I'm trying to open a recordset and 1

[solo7]

I'm trying to open a recordset and insert all the values from a specified table. I then want to be able to read each record one by one so I can manipulate some SQL code depening on the field values.


Dim Rs1 As New ADODB.Recordset
Dim SQLCode, StrData(5) As String
Dim cnn As ADODB.Connection
Dim Response, IntX, IntN As Integer

Set Rs1 = New ADODB.Recordset
Set cnn = CurrentProject.Connection
SQLCode = "SELECT * FROM TblModels"

Rs1.Open SQLCode, cnn

'StrData(1) = Rs1.Fld_ModelNo
'StrData(2) = Rs1.Fld_Manufacturer
'StrData(3) = Rs1.Fld_ModelType
'StrData(4) = Rs1.Fld_RetailPrice
'StrData(5) = Rs1.Fld_QtyAvailiable



I know the above code doesn't work - but how do I asign the field values to the StrData array one record at a time an loop through the whole table / recordset ??

Steady ...

 

[cmmrfrds]

There are 2 methods in the recordset object that will return the recordset in a string or array.

Dim vData as Variant
vData = rs1.GetString
Debug.print vData

vData = rs1.GetRows
Debug.print vData(0,1)
Debug.print vData(1,0)
'- Be aware that the rows and columns are reversed with get rows so the second dimension is the row.

Check these out.

 

[solo7]

cmmrfrds ... that's a great start thank you!

I need to loop through each record that is in the recordset I have now got. Presumably this is going to be a 'Do While' or a 'For Next'
I can see the recordset RecordCount property for a 'For Next' loop but I can't seem to be able to get it to return any value other than -1.
I can also see the EOF value but that seems to be only used with open files ??

Can you clue me in on how to reference each record of a recordset ???????

Steady ...

 

[cmmrfrds]

After the open statement the recordset is available and can be put directly in an array by the GetRows.

Rs1.Open SQLCode, cnn
Dim vData as Variant
vData = rs1.GetRows '- this fills the the array
'-- now you can step through the array

You don't always get a record count on the open, it depends on the parameters used. The default is for no locks and read only, which does not return a record count i.e.-1.

rs1.CursorLocation = adUseClient
Rs1.Open SQLCode, cnn, 3, 3 '- this will give a record count
First 3 is open static
2nd 3 is lock pessimistic

'- reference each record in loop if you want but the get rows would be a short cut to filling the array.

While Not rs1.EOF
var1 = rs1!field1
var2 = rs1!field2
etc.....
rs1.MoveNext
Wend

Hope this gets you going.

 

[solo7]

That's a great help Many, Many thanks.

Steady ...