Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Set a Primary Index using VBA

Posted on 2004-10-20
5
Medium Priority
?
1,238 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 1000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

704 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