Solved

Set a Primary Index using VBA

Posted on 2004-10-20
5
1,211 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

737 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