• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1265
  • Last Modified:

Set a Primary Index using VBA

This code builds a table, including an AutoNumber ID Field, which I want to be the Primary Key
How do I apply the Index to the ID Field?

Private Sub BuildSQLTable()

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb
Set tbl = db.CreateTableDef("TableName")

Set idx = tbl.CreateIndex("PrimaryKey")
With idx
    .Primary = True
    .Required = True
    .Unique = True
End With

Set fld = tbl.CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField
tbl.Fields.Append fld

6 more Fields...

db.TableDefs.Append tbl
End Sub
0
chrisezard
Asked:
chrisezard
  • 3
  • 2
1 Solution
 
stevbeCommented:
Make the field first and then .Append the field to the index's field collectionn and then finally .Append index to the table.

Set db = CurrentDb
Set tbl = db.CreateTableDef("TableName")

Set fld = tbl.CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField
tbl.Fields.Append fld

Set idx = tbl.CreateIndex("PrimaryKey")
With idx
    .Fields.Append fld
    .Primary = True
    .Required = True
    .Unique = True
End With
tbl.Indexes.Append idx


Steve
0
 
chrisezardAuthor Commented:
Thanks, Steve
The solution makes sense, but I get this error message...

With idx
    .Fields.Append fld   'Error 3367 Cannot append. An object with that name already exists in the collection.'

Chris
0
 
stevbeCommented:
tested and everything :-) It seems that the "Field" object as created by Index.CreateField and tbl.CreateField are not compatible so you need to "CreateField" twice.

Public Sub BuildSQLTable()

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
   
    Set db = CurrentDb
    Set tbl = db.CreateTableDef("TableName")
   
    Set fld = tbl.CreateField("ID", dbLong)
    fld.Attributes = dbAutoIncrField
    tbl.Fields.Append fld
   
    Set idx = tbl.CreateIndex("PrimaryKey")
    idx.Primary = True
   
    Set fld = idx.CreateField("ID")
    idx.Fields.Append fld
   
    tbl.Indexes.Append idx
   
    db.TableDefs.Append tbl

End Sub

Steev
0
 
chrisezardAuthor Commented:
Thanks, Steve
Worth knowing that TableDef.Fields and Index.Fields are two different collections

Chris
0
 
stevbeCommented:
one other thing I forgot to mention ...

    .Primary = True
    .Required = True
    .Unique = True

If you set the .Primary = True it will automatically set .Required and .Unique to True so you do not have to write the code for it but there certainly is no harm in having there for clarity of code reading if you want to.

Steve
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now