create primary key via VBA

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.
 
A

Allen Browne

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.
 
J

JTRockville

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top