Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Set a Primary Index using VBA

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

927 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