Set Primairykey to ID (also autonumber).

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda - 19 Aug 2004 12:26 GMT

Here's the code to create a table "tblTest" and create two
fields (ID and Name).
I want to set the first field "ID" as Primairykey and also
set it as autonumber.
The problem is I can't set it to an autonumber-field.
In the example it become a long integer instead of an
autonumber.
Can somebody solve this problem.
Please write some code so I can copy and paste it

Sample:
Dim db As Database
Dim tdf As TableDef
Dim strTable As String
Dim fldTemp As Field

Set ws = DBEngine.Workspaces(0)
Set tdf = CurrentDb.CreateTableDef("tblTest")
tdf.Fields.Append tdf.CreateField("ID", dbLong)
Set fldTemp = tdf.CreateField("Veld1", dbText, 255)
fldTemp.AllowZeroLength = True
CurrentDb.TableDefs.Append tdf

Reply to this Message

Roger Carlson - 19 Aug 2004 13:21 GMT

Oddly, in code, Autonumber is a property rather than a datatype. Something
like this:

Set fld = tdf.CreateField("ID", dbLong)
' Set field properties
fld.Attributes = dbAutoIncrField
' Append field to Fields collection
tdf.Fields.Append fld

Here's a complete example that you can modify:

Sub exaCreateTableWithAutoNumberPrimaryKey()
'DAO DDL example
'demonstrates creating a table, fields, properties, primary key
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

' Create the table and a field
Set db = CurrentDb()
Set tdf = db.CreateTableDef("NewTable")
Set fld = tdf.CreateField("AutoField", dbLong)
' Set field properties
fld.Required = True
fld.Attributes = dbAutoIncrField
' Append field to Fields collection
tdf.Fields.Append fld
' Create index
Set idx = tdf.CreateIndex("PrimaryKey")

' Append fields to index
Set fld = idx.CreateField("AutoField")
idx.Fields.Append fld
' Make Index primary
idx.Primary = True

' Append index to table
tdf.Indexes.Append idx
' Append table to TableDef collection
db.TableDefs.Append tdf

End Sub

Signature

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

> Here's the code to create a table "tblTest" and create two
> fields (ID and Name).
[quoted text clipped - 18 lines]
> fldTemp.AllowZeroLength = True
> CurrentDb.TableDefs.Append tdf

Reply to this Message

Tim Ferguson - 19 Aug 2004 23:06 GMT

> Here's the code to create a table "tblTest" and create two
> fields (ID and Name).
> I want to set the first field "ID" as Primairykey and also
> set it as autonumber.

...

> Please write some code so I can copy and paste it

You can do everything in three lines of VBA. Bear in mind that there is no
error trapping, so you'd normally want to protect the .Execute with an On
Error Resume Next. Otherwise, it's pretty straightforward, and much easier
than messing about with all those dao objects.

By the way, to my taste ID is too short a name to be useful, and using Name
is just a bug waiting to happen, so I have renamed things a bit. You can of
course change them back.

' do it all with one command
strSQL = "CREATE TABLE Test (" & _
"IDNum INTEGER IDENTITY(1,1) " & _
" CONSTRAINT pk PRIMARY KEY, " & _
"EnglishName VARCHAR(32) NULL " & _
")"

' use DAO; can also use ADO if you prefer
Set db = CurrentDB()

' make it so
db.Execute strSQL, dbFailOnError

Hope it helps

Tim F

Reply to this Message