Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Creating Indexes in DAO Code (2000)

    I am creating tables in a backend db in code using DAO -
    Set fld = tbl.CreateField(strFieldName, dbText, 50)
    tbl.Fields.Append fld
    but I need to set the Primary key(s) and indexes. Can someone help with code to do this please.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Indexes in DAO Code (2000)

    Here is an example of DAO code to create indexes, with lots of comments. If you paste this code into a module, you can click on words and press F1 to get help on them.

    Sub CreateIdx()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field

    On Error GoTo ErrHandler

    ' Set reference to database and table
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("tblSomething")

    ' Create a new index
    Set idx = tdf.CreateIndex("PrimaryKey")
    ' Create field for key - it must exist in the table
    Set fld = idx.CreateField("Field1")
    ' Add it to the index
    idx.Fields.Append fld
    ' Make this index the primary index
    idx.Primary = True
    ' Add it to the table
    tdf.Indexes.Append idx

    ' Create another new index
    Set idx = tdf.CreateIndex("OtherKey")
    ' Create two fields for this index and add them
    ' In other words, this is a composite index
    Set fld = idx.CreateField("Field2")
    idx.Fields.Append fld
    Set fld = idx.CreateField("Field3")
    idx.Fields.Append fld
    ' Make it a unique key (but not primary)
    idx.Unique = True
    ' Add it to the table
    tdf.Indexes.Append idx

    ExitHandler:
    ' Clean up
    Set fld = Nothing
    Set idx = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating Indexes in DAO Code (2000)

    Thanks for the great reply

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •