I always use this query in sql server to get Row number in a table:
SELECT *
FROM (SELECT *,
Row_number()
OVER(
ORDER BY [myidentitycolumn]) RowID
FROM mytable) sub
WHERE rowid = 15
Now I am working in Access 2010 and this seems to be not working. Is there any replacement for this query in Access?
Since I am sorting alphabetically on a string field and NOT by ID, the Count(*) and DCOUNT() approaches didn't work for me. My solution was to write a function that returns the Row Number:
Option Compare Database
Option Explicit
Private Rst As Recordset
Public Function GetRowNum(ID As Long) As Long
If Rst Is Nothing Then
Set Rst = CurrentDb.OpenRecordset("SELECT ID FROM FileList ORDER BY RealName")
End If
Rst.FindFirst "ID=" & ID
GetRowNum = Rst.AbsolutePosition + 1
' Release the Rst 1 sec after it's last use
'------------------------------------------
SetTimer Application.hWndAccessApp, 1, 1000, AddressOf ReleaseRst
End Function
Private Sub ReleaseRst(ByVal hWnd As LongPtr, ByVal uMsg As Long, ByVal nIDEEvent As Long, ByVal dwTime As Long)
KillTimer Application.hWndAccessApp, 1
Set Rst = Nothing
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With