CODEWITHSUNDEEP
ms-accessddl
samiksha
samiksha

Reputation: 73

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

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?

Upvotes: 6

Views: 27162

Answers (7)

NewSites
NewSites

Reputation: 1739

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:

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.

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

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

Upvotes: 2

LucienAkle
LucienAkle

Reputation: 1

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

Upvotes: -1

ProtoVB
ProtoVB

Reputation: 783

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

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123419

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

Upvotes: 3

Jim Lahman
Jim Lahman

Reputation: 2757

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

Upvotes: 1

Nayeem
Nayeem

Reputation: 58

Try this On

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

Upvotes: 0

Jacob
Jacob

Reputation: 43219

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

Upvotes: 6

Related Questions