This Access tutorial explains how to add auto number in Access Query using Access VBA Function.
Add Auto number in Access Table
In Microsoft Access, go to Design View of a Table and define the Data Type of a field as AutoNumber
Go to data view of the Table, each row of data is assigned a sequence number in ascending order.
However, we cannot add the AutoNumber Data Type in Query Design View, therefore we need to workaround to add auto number in Access Query.
Add Auto number in Access Query
To add auto number in Access Query, there are several solutions in Google. Some solutions have very low performance for large data set, some are related to assign ranking (not assigning sequence as is). Among all solutions, I prefer the one from tek-tips.
This solution creates a VBA Function, then you can use the Function directly in Query Expression.
Step 1 – Create User Defined Function
Press ALT + F11 > insert a Module > copy and below and paste to the Module.
Global seqNumber As Long Global lastcall As Date Function wAutoNumber(i) As Long If Now - lastcall > 4 / 60 / 60 / 24 Then lastcall = Now seqNumber = 0 End If seqNumber= seqNumber+ 1 wAutoNumber = seqNumber End Function
This Function adds 1 each time the Function is run. The Function is run from row one data to the last row of the Query, therefore row 1 will have an auto number of 1, row 2 will have 2.
The auto number (seqNumber) is stored as a Global variable, meaning the variable will not be reset to zero even after the Function is ended. Therefore we need to define when the variable will reset. The statement
Now - lastcall > 4 / 60 / 60 / 24
defines 4 seconds as the time for reset, you can change 4 to other number. Note that if you run this Query within 4 seconds, the auto number will continue to increase.
If you try to run a Macro to export more than 1 Queries without waiting for 4 seconds, try the following Sub
Public Sub exp() seqNumber= 0 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", "C:\test\Query1.xlsx", True seqNumber= 0 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query2", "C:\test\Query2.xlsx", True End Sub
Step 2 – Add Expression in Query
Add an Expression in a Query using the above User Defined Function. Actually the parameter is meaningless, it can be any field name, it is added so that the Function can work probably.
Run the Query, now the auto number is generated in a new field.
Note that the number begins with 2 instead of 1, I believe it is because Access runs the Function before running the Function for the first record in this Datasheet View.
However, if you export the Query to Excel, you will see the number begins with 1.
If you want the auto number to start with 1 in Datasheet View (but it will start with 0 if you export the data), simply replace
seqNumber = 0