用于复制ROW_NUMBER的Microsoft Access查询

SELECT 
    (SELECT COUNT(*) 
     FROM YourTable AS T2 
     WHERE T2.ID <= T1.ID) AS RowNumber,
    T1.*
FROM YourTable AS T1
ORDER BY T1.ID;

Public Function RowNumber(query As String, orderBy As String) As Recordset
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    
    Set db = CurrentDb()
    sql = "SELECT *, (SELECT COUNT(*) FROM (" & query & ") AS T2 WHERE T2." & orderBy & " <= T1." & orderBy & ") AS RowNumber FROM (" & query & ") AS T1 ORDER BY " & orderBy & ";"
    Set rs = db.OpenRecordset(sql)
    
    Set RowNumber = rs
End Function