primary key

[Bill Linares]

How do you define a field as primary key in VBA?
Is sounds trivial, but I have not been able to find answer in on-line help

 

[Allen Browne]

Using DAO, create an Index, and set its Primary property to Yes:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("MyTable")

'Create a new index. The name us usual, but not crucial.
Set ind = tdf.CreateIndex("PrimaryKey")
'Specify the field(s) in the index
With ind
.Fields.Append .CreateField("MyField")
.Primary = True 'This makes it the primary key
End With
'Add this new index to the TableDef's Indexes collection.
tdf.Indexes.Append ind

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing

 

[Bill Linares]

Why is it that I cannot append the table to the data-base?: Following line

db.TableDefs.Append tdf

produces error 3264, no field defined

Thanks for your troubles.

 

[Allen Browne]

First you CreateTableDef().
Then CreateField(), and append it to the TableDef.
Finally, Append the TableDef to the database's TableDefs.

 

[TC]

You have to create the fields in the *table* before you add them to the
index.

TC

 

[Bill Linares]

Isn't that what I'm doing?:

Public Sub Test_AutoKey()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.CreateTableDef("MyTable")

'Create a new index. The name us usual, but not crucial.
Set ind = tdf.CreateIndex("PrimaryKey")
'Specify the field(s) in the index
With ind
.Fields.Append .CreateField("MyField")
.Primary = True 'This makes it the primary key
End With
'Add this new index to the TableDef's Indexes collection.
tdf.Indexes.Append ind
db.TableDefs.Append tdf

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[Allen Browne]

You have created a table, but the table has no fields.
Try this sequence:
1. CreateTableDef
2. CreateField
3. Append field to TableDef
4. Repeat steps 2 and 3 for other fields.
5. Append TableDef to TableDefs.

6. Create the index
7. CreateField in index (doesn't create a field, just a reference to
it).
8. Append field to Index's Fields
9. Append Index to TableDef's Indexes.

 

[Bill Linares]

Thanks mate