Rownum in Access

[DNKMCA]

In Oracle, i can retrieve the row number of selected records by using
rownum.

Example:
Select rownum, id, name from MyTable;

But how can i retrieve the row number of a record in Ms-Access (Equivalent
to rownum in Oracle)??

Thanks

 

[Allen Browne]

Recordsets in Access have an AbsolutePosition.
Forms have a CurrentRecord property.

In general, we do not use this approach in Access for identifiying records,
finding or returning to records.

More info:
What no record numbers?
at:
http://allenbrowne.com/xbase-03.html

 

[DNKMCA]

Im using MS Excel as database.

I want to get the rowid of the row where my search value is found.

How to acheive this thru ADO.

Thanks.

 

[Allen Browne]

ADO recordsets have an AbsolutePosition

 

[Arvin Meyer [MVP]]

You might ask in an Excel newsgroup. As Allen mmentioned, row numbers are
irrelevant in a relational database where rows can be resorted in numerous
orders. Here's some code from the help files that will get the absolute
position of the row in the recordset. This example uses the pubs sample
database in SQL-Server:

Public Sub AbsolutePositionX()

Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String

' Open a recordset for the Employee table
' using a client cursor.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable

' Enumerate Recordset.
Do While Not rstEmployees.EOF
' Display current record information.
strMessage = "Employee: " & rstEmployees!lName & vbCr & _
"(record " & rstEmployees.AbsolutePosition & _
" of " & rstEmployees.RecordCount & ")"
If MsgBox(strMessage, vbOKCancel) = vbCancel _
Then Exit Do
rstEmployees.MoveNext
Loop

rstEmployees.Close

End Sub


--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[DNKMCA]

Hi,

recordset.absolute position gives the position of the fetched records in a
record set.

but in case of MS Excel as Backend,

if my search value is found in Sheet1's 25,000 Row,

how do i extract that the searvh value is found in row 25,000


Thanks