In an MS Access Maketable query, how to create an Autonumber field?

I have a Maketable query in an Access db that could use an Autonumber field. I can't find a built-in function for my purpose. Do i need to write my own? Ideally, I'd just like to create a field in the Access designer as "Autonum: CreateAutoNumber()"

Edit: If it can't b开发者_Go百科e done in the query itself, I can also run a procedure afterward.

---

I reckon you either need to use TableDefs or DDL. The DDL query would run after the maketable query, for example:

 ALTER TABLE NewTable ADD COLUMN AutoField COUNTER

EDIT Additional note

If you wish to make the new column the primary key, you can run something like:

ALTER TABLE NewTable ADD PRIMARY KEY (AutoField)

---

make your life simple, create a delete query followed by an append query, then use a macro to run them. if you need the autonumbering to start from 1 every time, create another query from your table with an extra column with the following code idnew:dcount("[id]","mytable","[id]<"&[id]) hope this helps.

---

I'd use vba

ALTER TABLE tbl_YourTable ADD [ID] AUTOINCREMENT PRIMARY KEY NOT NULL

---

1. Create an empty table containing your schema (use table design or copy existing table without data) and add an Autonumber field. Call this table yourtable_FORMAT
2. Copy yourtable_FORMAT to yourtable (each time you want to do a make table)
3. APPEND records to yourtable. (This causes the Autonumber field to always restart at 1.)

(This process also lets you append LOTS of tables together without using a UNION command.)