Set Primairykey to ID (also autonumber).

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

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
 
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
 
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
Click to expand...

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
 
Back
Top