Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2002
    Tasmania, Australia
    Thanked 0 Times in 0 Posts

    Assign index using VBA (97/2002)

    Hi all,
    I import tables from an accounting software package via an ODBC driver using the TransferDatabase Method. However to get the tables into a usable state I need to change the odd field to indexed(Duplicates OK). I've looked at the Tabledef samples of code in help, but can't get my head around the object model. <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15> Can anybody provide a sample of code to change one field in one table from not indexed to indexed(duplicates ok)?

    Thanks in advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Assign index using VBA (97/2002)

    Here is an example. The AddIndex procedure takes 3 arguments:

    Name of the table to be modified (must exist)
    Name of the field to be indexed (must exist)
    Name of the index to be created (must *not* exist, you must provide a new name)

    Usage example:

    AddIndex "Table1", "Field1", "MyNewIndex"

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub AddIndex(TableName As String, FieldName As String, IndexName As String)

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field

    On Error GoTo Err_AddIndex

    ' Reference to current database
    Set dbs = CurrentDb
    ' Reference to table
    Set tdf = dbs.TableDefs(TableName)
    ' Create new index
    Set idx = tdf.CreateIndex(IndexName)
    ' Create field for index
    Set fld = idx.CreateField(FieldName)
    ' Append the dield to the field list of the index
    idx.Fields.Append fld
    ' Append the index to the list of indexes of the table
    tdf.Indexes.Append idx
    ' Make sure the database engine knows about it


    ' Clean up
    Set fld = Nothing
    Set idx = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

    Exit Sub


    ' Inform user what went wrong
    MsgBox Err.Description, vbExclamation
    ' Goto clean up
    Resume Exit_AddIndex

    End Sub

Posting Permissions

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