Creating Primary Keys with VBA

[zandsc1]

I've done some research on this, and by all accounts this code should work from what I've read.

Dim db As Database
Dim fld As Field
Dim idx As Index
Dim tblDef As TableDef

Set db = CurrentDb

'create DMR table
Set tblDef = db.CreateTableDef("tblDMR*")

'create index
Set idx = tblDef.CreateIndex("PrimaryKey")
Set fld = idx.CreateField("DMR", dbText)
idx.Fields.Append fld
idx.Primary = True
tblDef.Indexes.Append idx

Set fld = tblDef.CreateField("Description", dbText)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("Revision", dbText)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("CreatedOn", dbDate)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("Author", dbText)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("PE", dbText)
tblDef.Fields.Append fld

db.TableDefs.Append tblDef

The code runs fine until the last line, where I get 'Run Time Error '3409':Invalid field definition 'DMR' in definition of index or relationship.'

Thoughts? Am I setting the fld incorrectly? Note that removing the ", dbText" from the fld definition does not solve the issue.

 

[PHV]

You try to creare an index on a non-existent field ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zandsc1]

I found the solution in another thread. Hurray for SQL!

Dim db As Database
Dim fld As Field
Dim tblDef As TableDef

Set db = CurrentDb

'create DMR table
Set tblDef = db.CreateTableDef("tblDMR")

'create fields
Set fld = tblDef.CreateField("DMR", dbText)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("Description", dbText)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("Revision", dbText)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("CreatedOn", dbDate)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("Author", dbText)
tblDef.Fields.Append fld
Set fld = tblDef.CreateField("PE", dbText)
tblDef.Fields.Append fld

'append table
db.TableDefs.Append tblDef

'create primary key
DoCmd.RunSQL "ALTER TABLE tblDMR ADD CONSTRAINT PrimaryKey PRIMARY KEY (DMR)"

 

[PHV]

Hurray for SQL!
So, why not simply use the CREATE TABLE instruction ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zandsc1]

At this point, it's not broken, so I'm not fixing it.


Going forward, it would probably be more efficient to use SQL for everything.