Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    1

    Unanswered: How do I create an index from VB6?

    How do I create an index in an Access database? I want to first create the table, then the index and then the second field in the table. Here's what my table should look like:

    * Table name: tblPrograms
    * First field in table is the "ID" field which is to be an Autonumber field
    with Indexed:=Yes (No Duplicates)
    * Second field is to be called "Programs" and to contain text only.

    How do I do it?

    Here's code that i'm trying to use:

    ' Create the new tblPrograms table.
    Set tblDefNew = dbChoice.CreateTableDef("tblPrograms")
    Set fldAppenNewIndex = tblDefNew.CreateIndex("ID")
    Set fldAppenNewField = fldAppenNewIndex.CreateField("ID", dbLong)
    fldAppenNewIndex.Primary = True
    fldAppenNewIndex.Unique = True
    tblDefNew.Indexes.Append fldAppenNewIndex
    Set fldAppenNewField = tblDefNew.CreateField("Program", dbText)
    tblDefNew.Fields.Append fldAppenNewField
    dbChoice.TableDefs.Append tblDefNew

    But I'm getting an error on the last line- "Runtime Error 3264- No field
    defined- cannot append TableDef or Index."

    I can't understand what I'm doing wrong?

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi thief_,

    Here's an example subroutine to create a table and an index. You have to pass it a database object.

    Also, you need to have the DAO library selected.
    Code:
    Public Sub CreateMyTable(dbToCreate As Database)
    
       'example of how to create a table with an index
    
       'Table "MyTable" will be create with two fields:
       '  MyAutonumberField
       '  MyNameField
    
       'Index "MyIndex" is created as a unique, non-primary
       'on field "MyAutonumberField"
       
       Dim fldNew() As New Field
       Dim indNew As Index
       Dim intField As Integer
       Dim tblNew() As New TableDef
       ReDim tblNew(1 To 1)
       ReDim fldNew(0 To 1)
    
       'Create the fields
       Set fldNew(0) = tblNew(1).CreateField("MyAutonumberField", dbLong)
           fldNew(0).Attributes = 17
       Set fldNew(1) = tblNew(1).CreateField("MyNameField", dbText, 50)
           fldNew(1).Attributes = 2
    
       'Append the new fields to the table
       For intField = LBound(fldNew) To UBound(fldNew)
          tblNew(1).Fields.Append fldNew(intField)
       Next
    
       'Name the table
       tblNew(1).Name = "MyTable"
    
       'Add the table to the collection
       dbToCreate.TableDefs.Append tblNew(1)
       dbToCreate.TableDefs.Refresh
    
       'Name the index and set the properties
       Set indNew = tblNew(1).CreateIndex("MyIndex")
       indNew.Primary = False
       indNew.Unique = True
    
       'Specify fields to be included in the index
       ReDim fldNew(1 To 1)
       fldNew(1).Name = "MyAutonumberField"
    
       'Append the fields to the index
       indNew.Fields.Append fldNew(1)
    
       'Append the index to the table
       tblNew(1).Indexes.Append indNew
    End Sub
    Hope that helps.
    JT

Posting Permissions

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