primary key

  • Thread starter Thread starter Bill Linares
  • Start date Start date
B

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
 
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
 
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.
 
First you CreateTableDef().
Then CreateField(), and append it to the TableDef.
Finally, Append the TableDef to the database's TableDefs.
 
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
 
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.
 
Back
Top