Rownum in Access

D

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
 
A

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
 
D

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.
 
A

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top