create primary key via VBA

  • Thread starter Thread starter Harry
  • Start date Start date
H

Harry

Hi to all. I have a existing table and would like to
change several fields to indexed (not unique) and one of
these fields to be a primary key. Can this be done by
VBA? Example if possible.

Thanks.
 
To create an index:
- use the CreateIndex method;
- use the CreateField method to specify the field(s) in the index, and
Append to the Fields of the index;
- Append the new index to the Indexes of the TableDef.
The default is "not unique".

To make the index the primary key, set its Primary property to true.

Example:
Set db = CurrentDb()
Set tdf = db.TableDefs("MyTable")
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("MyIdField")
.Primary = True
End With
tdf.Indexes.Append ind

Note: assumes a reference to the DAO library.
 
Hi Harry,

You'll need to have the Microsoft DAO Object library in
your references to use this.

You can change the "primary" and "unique" properties to
meet your needs.

Is that what you were looking for?

JT

Sub SampleCreateIndex()

'example of how to an index

'Table "MyTable" has two fields:
' MyField1
' MyField2

'Index "MyIndex" is created as non-unique, non-primary
'on fields "MyField1" and "MyField2"

Dim fldNew() As New Field
Dim indNew As New Index

Set indNew = CurrentDb.TableDefs("MyTable").CreateIndex
("MyIndex")
indNew.Primary = False
indNew.Unique = False

ReDim fldNew(1 To 2)
fldNew(1).Name = "MyField1"
fldNew(2).Name = "MyField2"

indNew.Fields.Append fldNew(1)
indNew.Fields.Append fldNew(2)

CurrentDb.TableDefs("MyTable").Indexes.Append indNew
End Sub
 
Back
Top