How to create table with Autonumber field in MS - Access at run time?

Question

I am working with MS-Access and JSP. I want to know that how can we create table with autonumber field and with primary key.

query="Create Table Registration_A (Reg_No PRIMARY KEY AUTOINCREMENT, FName varchar(2))";

But its giving syntax error. What's the correct syntax?

Answer 1

CREATE TABLE Registration_A (
Reg_No AUTOINCREMENT, 
FName VARCHAR(2), 
CONSTRAINT RegA_PK PRIMARY KEY(Reg_No))

Answer 2

You can use the COUNTER keyword to create an AutoNumber field using DDL. I just tested this in a Java console app and it worked for me under both the JDBC-ODBC Bridge and UCanAccess:

String query = 
        "CREATE TABLE Registration_A (" +
            "Reg_No COUNTER PRIMARY KEY, " +
            "FName VARCHAR(2))";
Statement stmt = con.createStatement();
stmt.executeUpdate(query);