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);

Answer 3

This example uses ADOX to create a access table with an autonumber 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

You need to mention the data type first, then the Primary Key.

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

Answer 5

Seven years later, I don't see how to do this in DAO in any of the answers above or anywhere else on any Stack Exchange site. So here is the method I've worked out. The following VBA code creates a table with an autonumber field as primary key, puts some arbitrary data in it, then opens the table to show the results. I've run this code successfully in Access 2007.

Sub Make_Table_With_Autonum_Using_DAO()

Dim oDB As DAO.Database: Set oDB = CurrentDb()
Dim oTable  As DAO.TableDef, sObjTable As String: sObjTable = "table_name"
Dim oField As DAO.Field, oIndex As DAO.Index
Dim oRS As DAO.Recordset

Set oTable = oDB.CreateTableDef(sObjTable)
With oTable
    Set oField = .CreateField("ID_Object", dbLong)      ' Create ID field.
        oField.Attributes = dbAutoIncrField                 ' Make it autoincrement.
        .Fields.Append oField                               ' Add to table's Fields collection.
        Set oIndex = .CreateIndex("Index_Object")           ' Create index.
        oIndex.Primary = True                               ' Make it a primary key.
        Set oField = oIndex.CreateField("ID_Object")        ' Make index field for ID field.
        oIndex.Fields.Append oField                         ' Add it to index's Fields coll'n.
        .Indexes.Append oIndex                              ' Add index to table's Indexes coll'n.
        Set oIndex = Nothing                                ' Remove index from memory.
        Set oField = Nothing                                ' Remove field from memory.
    .Fields.Append .CreateField("field2", dbText)         ' Create and add other fields to
    .Fields.Append .CreateField("field3", dbInteger)      '       table's Fields collection.
    ' etc.
  End With
oDB.TableDefs.Append oTable                       ' Add table to database's TableDefs collection.
Set oTable = Nothing

Set oRS = oDB.OpenRecordset(sObjTable)            ' Enter arbitrary data into table.
oRS.AddNew: oRS!Field2 = "text 1": oRS!field3 = 123: oRS.Update
oRS.AddNew: oRS!Field2 = "text 2": oRS!field3 = 456: oRS.Update
oRS.AddNew: oRS!Field2 = "text 3": oRS!field3 = 789: oRS.Update
oRS.Close

DoCmd.OpenTable (sObjTable)
oDB.Close
Set oRS = Nothing
Set oDB = Nothing

End Sub

The Microsoft documentation for the necessary VBA elements, in order of appearance in the code, is:

• TableDef.CreateField method (DAO)
• Field.Attributes property (DAO)
• Fields.Append method (DAO)
• TableDef.CreateIndex method (DAO)
• Index.Primary property (DAO)
• Index.CreateField method (DAO)
• Indexes.Append method (DAO)

That documentation says everything that needs to be known, but doesn't put it all together to explain how to make the autonumber primary key. The following MS documentation (no longer available directly from MS) does explain how to make the autonumber field, but not how to make it the primary key.

• Programmatically Add Counter Field to Table Using DAO

In the following post on a Microsoft community forum, the accepted answer by Andrey Artemyev explains the whole thing.

• Create a new table with AutoNumber field with VBA

My code above is essentially the same as his in that answer, with some additional commentary to explain what's going on.

Answer 6

Try this On

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

Answer 7

CREATE TABLE `Tablename` (Field1 AUTOINCREMENT CONSTRAINT `Primarykey` PRIMARY
KEY, `Field2` DATETIME, `Field3` TEXT(25), `Field4` DOUBLE);