Create Table and AutoNumber

[TanisAgain]

I have managed to get this code to work to Create a Table. I have also managed to create a Primary Key, but, I have not been able to make the field an AutoNumber or AutoIncrement data type. Any ideas?

Dim ws As Workspace
Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim FLD As DAO.Field

'Get default Workspace
Set ws = DBEngine.Workspaces(0)
Set DB = CurrentDb

Set TD = DB.CreateTableDef("myTestTable")

'Create fields and data types
With TD
.Fields.Append .CreateField("MyTestField", dbText, 255)
.Fields.Append .CreateField("Field2", dbDouble, 2)
.Fields.Append .CreateField("Field3", dbInteger, 3)
.Fields.Append .CreateField("Field4", dbLong, 2)
.Fields.Append .CreateField("Field5", dbMemo)
.Fields.Append .CreateField("Field6", dbBoolean)
.Fields.Append .CreateField("Field7", dbCurrency)
End With

Set FLD = TD.CreateField("MyTestField", dbText, 255)
FLD.DefaultValue = "default Value"


Set idx = TD.CreateIndex("PrimaryKey")
Set FLD = idx.CreateField("Field4", dbLong)
idx.Fields.Append FLD
idx.Primary = True
TD.Indexes.Append idx

Set idx = TD.CreateIndex("MyTestField")
Set FLD = idx.CreateField("MyTestField")
idx.Fields.Append FLD
TD.Indexes.Append idx

DB.TableDefs.Append TD

 

[TanisAgain]

OK, I have managed to find the answer. I will post the code in case anyone else has the same problem.

Dim WS As Workspace
Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim FLD As DAO.Field


'Get default Workspace
Set WS = DBEngine.Workspaces(0)
Set DB = CurrentDb




Set TD = DB.CreateTableDef("myTestTable")

'Create fields and data types. These are all the available data types

With TD

You need to create a FLD object

Set FLD = .CreateField("fldID", dbLong)
FLD.Attributes = fldAttributes Or dbAutoIncrField
.Fields.Append FLD

.Fields.Append .CreateField("MyTestField", dbText, 255)
.Fields.Append .CreateField("Field2", dbDouble, 2)
.Fields.Append .CreateField("Field3", dbInteger, 3)
.Fields.Append .CreateField("Field4", dbLong)
.Fields.Append .CreateField("Field5", dbMemo)
.Fields.Append .CreateField("Field6", dbBoolean)
.Fields.Append .CreateField("Field7", dbCurrency)
End With


'Create a default value
Set FLD = TD.CreateField("MyTestField", dbText, 255)
FLD.DefaultValue = "default Value"

'Create a Primary Key
Set idx = TD.CreateIndex("PrimaryKey")
Set FLD = idx.CreateField("FldID", dbLong)
idx.Fields.Append FLD
idx.Primary = True
TD.Indexes.Append idx

'create an index
Set idx = TD.CreateIndex("MyTestField")
Set FLD = idx.CreateField("MyTestField")
idx.Fields.Append FLD
TD.Indexes.Append idx

DB.TableDefs.Append TD