Create or set Primairykey to ID and set autonumber (VBA).

[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

 

[Douglas J. Steele]

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

Set fldTemp = tdf.CreateField("ID", dbLong)
fldTemp.Attributes = fldTemp.Attributes + dbAutoIncrField
tdf.Fields.Append fldTemp

Set fldTemp = tdf.CreateField("Veld1", dbText, 255)
fldTemp.AllowZeroLength = True
tdf.Fields.Append fldTemp

CurrentDb.TableDefs.Append tdf

 

[Guest]

Thanks Doug Steele
One problem is solved, but you forgot to set it to a
primairy key.
Do you know the code for that.
Please change the code again if you can.

 

[PP]

how can I do this by sql command?

thanx...

PP

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

Set fldTemp = tdf.CreateField("ID", dbLong)
fldTemp.Attributes = fldTemp.Attributes + dbAutoIncrField
tdf.Fields.Append fldTemp

Set fldTemp = tdf.CreateField("Veld1", dbText, 255)
fldTemp.AllowZeroLength = True
tdf.Fields.Append fldTemp

CurrentDb.TableDefs.Append tdf

 

[Roger Carlson]

As far as I can tell, you cannot do this directly in SQL. You must use VBA
to set the AutoIncrement attribute. If somebody knows different, I'd love
to see it.

 

[Roger Carlson]

Please don't post a question in multiple forums. I gave a complete answer
in the tabledbdesign forum, including setting the primary key.