Search code examples
ms-accesssql-insertautonumber

How to insert values into an Access table's AutoNumber field?

I'm working on a project using jsp and Microsoft Access.

There are three tables in which a same field is used as Receipt_No.
This can get from Recp table, which has a only one field: Receipt_No (AutoNumber, Primary Key).

Now my question is, how can I insert in this AutoNumber field with every generation of receipt?.

Solution

  • WARNING This is ghetto. (But then again I'm thinking a table with ONLY an autonumber field is kinda ghetto too, so oh well.)

    INSERT INTO yourTable (your_autonumber_field) SELECT max(your_autonumber_field) + 1 FROM yourTable

    Not elegant, but it works. That will create a new record in your Receipt table. It defeats the whole purpose of having an autonumber field but I don't see another way to use SQL to create a record in a table with only an autonumber field. You can then retrieve the newly created receipt ID with a SELECT max(your_autonumber_field) FROM yourTable for use in your FK fields in the other tables.