By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,246 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,246 IT Pros & Developers. It's quick & easy.

How to add index with CreateIndex ?

P: n/a
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???
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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???

Nov 12 '05 #2

P: n/a
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???


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.