MS Access VBA Index Field and Set Primary Key for versions 2000, 2002, 2003, 2007

Go here To Check if Table exists in VBA.
Go here To Check if Field exists in VBA
Follow the Update Field in VBA Link to update records in a table.
Follow the Get Indexed Fields with VBA Link to find all Indexes and Primary Key.

The following Access VBA Index Fields code snippet will use VBA to index multiple fields whithin a Table.

The VBA Index Field Sample is available for download here in a zipped MS Access Database. The below picture (click to enlarge) shows the Single form.

You should be able to copy/paste the below code right into a Module.

Function IndexField() As Boolean
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim Dindex As DAO.Index

   ' References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   ' Set references by Clicking Tools and Then References in the Code View window
   ' Returns True on Success, False otherwise.
   ' Accepts
   ' None
   ' Sets One field to Primary Key and indexes two others.
   ' USAGE: IndexField

   Set db = CurrentDb()
   'Set Tablename to your table mine is 1A
   Set tdf = db.TableDefs( "1A" )

   'Primary key Unique index, Indexed: Yes (No Duplicates)
   'Set Primary Key
   Set Dindex = tdf.CreateIndex( "PrimaryKey" )
   With Dindex
      'Select field to set as Primary Key, mine is RECNUM
      .Fields.Append .CreateField( "RECNUM" )
      .Unique = True
      .Primary = True
   End With
   tdf.Indexes.Append Dindex

   'Non-Unique index, Indexed: Yes (Duplicates OK)
   Set Dindex = tdf.CreateIndex( "STATE" )
   With Dindex
      'Select field to set non-unique Index, mine is STATE
      .Fields.Append .CreateField( "STATE" )
      .Unique = False
   End With
   tdf.Indexes.Append Dindex

   'Unique index, Indexed: Yes (No Duplicates).
   Set Dindex = tdf.CreateIndex( "PHONE" )
   With Dindex
      'Select field to set unique Index, mine is PHONE
      .Fields.Append .CreateField( "PHONE" )
      .Unique = True
   End With
   tdf.Indexes.Append Dindex

   'Refresh the collection.
   tdf.Indexes.Refresh

ExitHere:

   Set Dindex = Nothing
   Set tdf = Nothing
   Set db = Nothing

   'Notify the user the process is complete.
   MsgBox "Indexes created."
   Exit Function

errhandler: 'There is an error return as False
   IndexField = False

   With Err

      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "IndexField"

   End With

   Resume ExitHere

End Function

For the above VBA Index Field Function in an usable Form with Make Table Button, download the Sample database in ZIP format.

For further help or suggestions go here: MS Access tutorials, Tables, Queries and later: Forms

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.

For further MS Access tutorials go here: MS Access tutorials

Did you find what you were looking for? What would you suggest? mail@eraserve.com


Your Name:
Your Message:
Your Email:

Custom Search