MS Access VBA Find all Indexed Fields and Get 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 Index a Field with VBA Link to index a Field.

The following Access VBA Get Indexed Fields code snippet will use VBA retrieve all indexed fields within a table.

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

MS Access get all Indexed Fields VBA Form

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

Function GetIndexed(TableName As String ) As String
   Dim ind As DAO.Index, db As DAO.Database, fld As DAO.Field, tdf As DAO.TableDef
   Dim strField As String , intPass As Integer

   '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
   ' Get all Indexed Fields and find the Primary Key in table TableName.
   ' Accepts
   ' TableName: Name of table in which the field is located
   ' Returns the Primary Key and All Fields and their Indexes in a table
   'USAGE: GetIndexed ("TABLENAME")

   Set db = CurrentDb()

   Set tdf = db.TableDefs(TableName)

   'Loop through all fields
   For Each fld In tdf.Fields
      'Toggle to 1 to record fields with no Index
      intPass = 1

      For Each ind In tdf.Indexes

         If ind.Fields.Count = 1 Then
            If ind.Fields( 0 ).Name = fld.Name Then
               'Toggle to record fields with an Index
               intPass = 0

               If ind.Primary Then
                  'Record the field name and Primary Key value
                  strField = strField & fld.Name & " Primary Key = " & ind.Primary & vbCrLf
               ElseIf ind.Unique Then
                  'Record the field name and Unique Index value
                  strField = strField & fld.Name & " Yes (No Duplicates) " & ind.Unique & vbCrLf
               ElseIf ind.Unique = False Then
                  'Record the field name and Non-Unique Index value
                  strField = strField & fld.Name & " Yes (Duplicates OK)" & ind.Unique & vbCrLf
               End If
            End If
         End If
      Next

      'Record Fields with no Index
      If intPass = 1 Then
         'Record the field name
         strField = strField & fld.Name & vbCrLf
      End If
   Next

   GetIndexed = strField

ExitHere:

   Set db = Nothing
   Set ind = Nothing
   Set fld = Nothing
   Set tdf = Nothing
   'Notify the user the process is complete.
   MsgBox "GetIndex Complete"
   Exit Function

ErrHandler:
   With Err
      'There is an error return it
      GetIndexed = "Error " & .Number & vbCrLf & .Description & " GetIndexed"

   End With

   Resume ExitHere

End Function

For the above VBA Get Indexed Fields Function in an usable Form with Get Index 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