Access function or equivalent SQL expression ROW_NUMBER ()?

Question

Is there anything I can do in Access that mimics the behavior of the TSQL function ROW_NUMBER () when executing an INSERT query?

Answer 1

Old but kind:

How to rank records in a query
http://support.microsoft.com/kb/208946

Answer 2

Another option is to use a small part of VBA (that is, if you are really working in Access, and not just using an Access database)

Option Compare Database Dim rn As Long Function ResetRownumber() As String rn = 0 ResetRownumber = "OK" End Function Function RowNumber(dummyID As Integer) As Long If (dummyID > 0) Then rn = rn + 1 RowNumber = rn End If End Function 

You call ResetRownumber () before starting your request And then

 SELECT RowNumber(anyfield) AS RowNum, OtherField FROM SomeTable 

This will give you ripples. Passing one of the selected field names is optional, otherwise the VBA function is called only once, and not for each row. Warning: Use it only on a limited number of records, as your request will slow down significantly by invoking VBA code for each selected row.