Using ADO: locating and reading contents of fields from specific

[Guest]

Using an ADO recordset, how do i read the contents of a specific record
number's fields.

 

[Brendan Reynolds]

That rather depends on what you mean by 'record number'. The term doesn't
have any specific meaning in Access or Jet. Records don't have 'record
numbers' unless you implement them yourself, and recordsets have no defined
order unless you specify one in the source.

You can access a specific record by using a WHERE clause in the source ...

rst.Open "SELECT * FROM Table WHERE PkField = PkValue"

Or you can move to a record in an opened recordset. There are various
methods you can use, including the Find methods, Move methods, and
AbsolutePosition property ...

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.Source = "SELECT * FROM tblTest ORDER BY TestID"
.Open
.AbsolutePosition = 3
Debug.Print .Fields(0).Value
.Move -1
Debug.Print .Fields(0).Value
.Close
End With

.... but the important thing to remember here is that there is no permanent,
static reference to any specific record here - if I add or delete records
from the base table, or change the ORDER BY clause, and then re-run the
code, it will no longer be referring to the same records. When, in the code
above, I say 'AbsolutePosition = 3', this means the third record in
ascending order by TestID at this point in time. The same record may not be
the third record the next time I open the recordset.

 

[Guest]

Outstanding. Thank you Brendan.

That rather depends on what you mean by 'record number'. The term doesn't
have any specific meaning in Access or Jet. Records don't have 'record
numbers' unless you implement them yourself, and recordsets have no defined
order unless you specify one in the source.

You can access a specific record by using a WHERE clause in the source ...

rst.Open "SELECT * FROM Table WHERE PkField = PkValue"

Or you can move to a record in an opened recordset. There are various
methods you can use, including the Find methods, Move methods, and
AbsolutePosition property ...

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.Source = "SELECT * FROM tblTest ORDER BY TestID"
.Open
.AbsolutePosition = 3
Debug.Print .Fields(0).Value
.Move -1
Debug.Print .Fields(0).Value
.Close
End With

.... but the important thing to remember here is that there is no permanent,
static reference to any specific record here - if I add or delete records
from the base table, or change the ORDER BY clause, and then re-run the
code, it will no longer be referring to the same records. When, in the code
above, I say 'AbsolutePosition = 3', this means the third record in
ascending order by TestID at this point in time. The same record may not be
the third record the next time I open the recordset.