How to add index with CreateIndex ?

[deko]

The last 4 lines seem to be screwing up.... any suggestions welcome!

- - - - - - - - - - - - - -
Dim db As Database
Dim tdf As TableDef
Dim idxPk As Index
Dim idxFld As Field

Set tdf = db.CreateTableDef("tblAppointments") 'create new table
With tdf 'add fields
.Fields.Append .CreateField("ApptDate", dbDate)
.Fields.Append .CreateField("Subject", dbText, 100)
.Fields("Subject").AllowZeroLength = True
.Fields.Append .CreateField("Entity_ID", dbLong)
.Fields.Append .CreateField("Appt_ID", dbLong)
.Fields("Appt_ID").Attributes = dbAutoIncrField
End With
Set idxPk = tdf.CreateIndex("Appt_ID") 'make Appt_ID primary key
idxPk.Primary = True
idxPk.Unique = True
Set idxFld = idxPk.CreateField("Appt_ID") 'type mismatch here...
idxPk.Fields.Append idxFld 'add field to Fileds collection of index
object - this screws up, too...
tdf.Indexes.Append idxPk 'add index to index collection 'have not got
far enough to validate these nex lines...
db.TableDefs.Append tdf 'append tdf to TablsDefs collection

- - - - - - - - - - - -

What I'm trying to do is create a table programmatically and add a Primary
Key, and also perhaps another non-unique index.... if I can get the Primary
key first.... I read somewhere that I might need a separate tableDef to
create the PK???

[deko]

Ah ha...

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idxPk As DAO.Index
Dim idxFld As DAO.Field

Apparently Access has an identity crisis when it comes to data access...

"deko" <dj****@hotmail.com> wrote in message
news:H5*******************@newssvr29.news.prodigy. com...

The last 4 lines seem to be screwing up.... any suggestions welcome!

- - - - - - - - - - - - - -
Dim db As Database
Dim tdf As TableDef
Dim idxPk As Index
Dim idxFld As Field

Set tdf = db.CreateTableDef("tblAppointments") 'create new table
With tdf 'add fields
.Fields.Append .CreateField("ApptDate", dbDate)
.Fields.Append .CreateField("Subject", dbText, 100)
.Fields("Subject").AllowZeroLength = True
.Fields.Append .CreateField("Entity_ID", dbLong)
.Fields.Append .CreateField("Appt_ID", dbLong)
.Fields("Appt_ID").Attributes = dbAutoIncrField
End With
Set idxPk = tdf.CreateIndex("Appt_ID") 'make Appt_ID primary key
idxPk.Primary = True
idxPk.Unique = True
Set idxFld = idxPk.CreateField("Appt_ID") 'type mismatch here...
idxPk.Fields.Append idxFld 'add field to Fileds collection of index
object - this screws up, too...
tdf.Indexes.Append idxPk 'add index to index collection 'have not got
far enough to validate these nex lines...
db.TableDefs.Append tdf 'append tdf to TablsDefs collection

- - - - - - - - - - - -

What I'm trying to do is create a table programmatically and add a Primary
Key, and also perhaps another non-unique index.... if I can get the Primary key first.... I read somewhere that I might need a separate tableDef to
create the PK???

[Larry Linson]

No "identity crisis", just two "data access methods" which happen to share
some object names but are not compatible. It's a long story, told before,
and could be googled up, I'd wager.

"deko" <dj****@hotmail.com> wrote in message
news:3g*******************@newssvr25.news.prodigy. com...

Ah ha...

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idxPk As DAO.Index
Dim idxFld As DAO.Field

Apparently Access has an identity crisis when it comes to data access...

"deko" <dj****@hotmail.com> wrote in message
news:H5*******************@newssvr29.news.prodigy. com...

The last 4 lines seem to be screwing up.... any suggestions welcome!

- - - - - - - - - - - - - -
Dim db As Database
Dim tdf As TableDef
Dim idxPk As Index
Dim idxFld As Field

Set tdf = db.CreateTableDef("tblAppointments") 'create new table
With tdf 'add fields
.Fields.Append .CreateField("ApptDate", dbDate)
.Fields.Append .CreateField("Subject", dbText, 100)
.Fields("Subject").AllowZeroLength = True
.Fields.Append .CreateField("Entity_ID", dbLong)
.Fields.Append .CreateField("Appt_ID", dbLong)
.Fields("Appt_ID").Attributes = dbAutoIncrField
End With
Set idxPk = tdf.CreateIndex("Appt_ID") 'make Appt_ID primary key
idxPk.Primary = True
idxPk.Unique = True
Set idxFld = idxPk.CreateField("Appt_ID") 'type mismatch here...
idxPk.Fields.Append idxFld 'add field to Fileds collection of index
object - this screws up, too...
tdf.Indexes.Append idxPk 'add index to index collection 'have not got far enough to validate these nex lines...
db.TableDefs.Append tdf 'append tdf to TablsDefs collection

- - - - - - - - - - - -

What I'm trying to do is create a table programmatically and add a Primary Key, and also perhaps another non-unique index.... if I can get the

Primary

key first.... I read somewhere that I might need a separate tableDef to
create the PK???