How to create a table with an Autonumber field in MS - runtime access?

Question

I work with MS-Access and JSP. I want to know how we can create a table with an autonumber field and with a primary key.

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

But its a syntax error. What is 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 application, and it worked for me both on the JDBC-ODBC bridge and on UCanAccess :

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

Answer 3

In this example, ADOX uses autonumber to create an access table with a primary key.

 ADOX.Catalog cat = new ADOX.Catalog(); ADOX.Table table = new ADOX.Table(); ADOX.Key tableKey = new Key(); ADOX.Column col = new Column(); String connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.accdb; Jet OLEDB:Database Password="; cat.Create(ConnString); // Define column with AutoIncrement features col.Name = "ID"; col.Type = ADOX.DataTypeEnum.adInteger; col.ParentCatalog = cat; col.Properties["AutoIncrement"].Value = true; table.Name = "Security"; table.Columns.Append(col); // default data type is text[255] table.Columns.Append("Username", ADOX.DataTypeEnum.adVarWChar, 255); table.Columns.Append("Password", ADOX.DataTypeEnum.adVarWChar, 255); table.Columns.Append("Engineer", ADOX.DataTypeEnum.adBoolean); table.Columns.Append("Default", ADOX.DataTypeEnum.adBoolean); // Set ID as primary key tableKey.Name = "Primary Key"; tableKey.Columns.Append("ID"); tableKey.Type = KeyTypeEnum.adKeyPrimary; // Add table to database cat.Tables.Append(table); 

Answer 4

First you need to specify the data type, then the Primary Key.

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

Answer 5

Try to enable

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