Solved

Set a Primary Index using VBA

Posted on 2004-10-20
5
1,221 Views
Last Modified: 2012-05-05
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
Comment
Question by:chrisezard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 12358338
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
 
LVL 1

Author Comment

by:chrisezard
ID: 12358678
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
 
LVL 39

Accepted Solution

by:
stevbe earned 250 total points
ID: 12358874
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
 
LVL 1

Author Comment

by:chrisezard
ID: 12359916
Thanks, Steve
Worth knowing that TableDef.Fields and Index.Fields are two different collections

Chris
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12360488
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question