Question: 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?

Answer: 
Another way to assign a row number in a query is to use the `DCount` function.

```
SELECT *, DCount("[ID]","[mytable]","[ID]<=" & [ID]) AS row_id
FROM [mytable]
WHERE row_id=15

```
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

```