locked
Determine if a field is Indexed RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I'm iterating through all fields in a table to get the field name, data type, required, etc.

    I can't figure out how to determine if the field is indexed and how it's indexed.

    "Yes (No Duplicates)"
    "Yes (Duplicates OK)"
    "No"

    Is there an easy way to do this?

    Thanks,
    Brad

    Thursday, October 5, 2017 6:49 PM

Answers

  • Thanks Hans!

    I actually found something similar on line, struggled with it but I think I have ended up with what you suggested... I must be learning :-)

    GetFieldIndex does what you have posted and finds out if a field is index and unique.

    GetIndex checks to see if the index is a group of fields and if it's unique.

    Please let me know if you see anything wrong.

    Function GetIndex(tblName As String) As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
    Dim indxFields As String
    Dim strField As String
     
    Set db = CurrentDb()
    Set tdf = db.TableDefs(tblName)
    Set rst = db.OpenRecordset(tblName, dbOpenTable)
     
    If GetIndex = "" Then
        ' List values for each index in the collection.
        For Each idx In tdf.Indexes
          ' The index object contains a collection of fields,
          ' one for each field the index contains.
            'Debug.Print idx.Fields.Count
            If idx.Fields.Count > 1 Then
                For Each fld In idx.Fields
                  If indxFields = "" Then
                    indxFields = fld.Name
                  Else
                    indxFields = indxFields & "," & fld.Name
                  End If
                  
                Next fld
                If Left(GetIndex, 5) = "INDEX" Then
                    GetIndex = GetIndex & vbCrLf & "CREATE " & IIf(idx.Unique = True, "UNIQUE ", "") & "INDEX " & "[" & idx.Name & "]" & vbCrLf & "ON " & tblName & " (" & indxFields & ")"
                Else
                    GetIndex = "CREATE " & IIf(idx.Unique = True, "UNIQUE ", "") & "INDEX " & "[" & idx.Name & "]" & vbCrLf & "ON " & tblName & " (" & indxFields & ")"
                End If
                indxFields = ""
            End If
        Next idx
        If GetIndex <> "" Then
            GetIndex = GetIndex & ";"
            'Debug.Print GetIndex
        End If
    End If
    
    End Function
    
    
    Function GetFieldIndex(tblName As String, fldName As String) As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
     
    Dim strField As String
     
    Set db = CurrentDb()
    Set tdf = db.TableDefs(tblName)
    Set rst = db.OpenRecordset(tblName, dbOpenTable)
     
    
    For Each idx In tdf.Indexes
        If idx.Name = fldName Then
            If idx.Unique = True Then
                GetFieldIndex = " NOT NULL UNIQUE"
            Else
                GetFieldIndex = " NOT NULL"
            End If
            Exit For
        End If
    Next idx
    
    End Function
    Thanks,
    Brad

    Thursday, October 5, 2017 7:48 PM

All replies

  • If you're using DAO, you have to loop through the Indexes property of the TableDef object.

    Each Index has a Fields collection that lists the fields contained in the index - an index can be on one field or on a combination of two or more fields. This also means that a field can be part of multiple indexes, of which some could allow duplicates and others not.

    Here is a very rough example:

    Sub Test()
        Const strTable = "tblEmployees" ' Table name
        Const strField = "strLastName" ' Field name
        Dim strMsg As String
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim idx As DAO.Index
        Dim fld As DAO.Field
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs(strTable)
        For Each idx In tdf.Indexes
            For Each fld In idx.Fields
                If fld.Name = strField Then
                    strMsg = "The field " & strField & _
                        " is part of the index " & idx.Name & vbCrLf
                    If idx.Unique Then
                        strMsg = strMsg & "This index is unique"
                    Else
                        strMsg = strMsg & "This index is not unique"
                    End If
                End If
                MsgBox strMsg, vbInformation
            Next fld
        Next idx
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 5, 2017 7:45 PM
  • Thanks Hans!

    I actually found something similar on line, struggled with it but I think I have ended up with what you suggested... I must be learning :-)

    GetFieldIndex does what you have posted and finds out if a field is index and unique.

    GetIndex checks to see if the index is a group of fields and if it's unique.

    Please let me know if you see anything wrong.

    Function GetIndex(tblName As String) As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
    Dim indxFields As String
    Dim strField As String
     
    Set db = CurrentDb()
    Set tdf = db.TableDefs(tblName)
    Set rst = db.OpenRecordset(tblName, dbOpenTable)
     
    If GetIndex = "" Then
        ' List values for each index in the collection.
        For Each idx In tdf.Indexes
          ' The index object contains a collection of fields,
          ' one for each field the index contains.
            'Debug.Print idx.Fields.Count
            If idx.Fields.Count > 1 Then
                For Each fld In idx.Fields
                  If indxFields = "" Then
                    indxFields = fld.Name
                  Else
                    indxFields = indxFields & "," & fld.Name
                  End If
                  
                Next fld
                If Left(GetIndex, 5) = "INDEX" Then
                    GetIndex = GetIndex & vbCrLf & "CREATE " & IIf(idx.Unique = True, "UNIQUE ", "") & "INDEX " & "[" & idx.Name & "]" & vbCrLf & "ON " & tblName & " (" & indxFields & ")"
                Else
                    GetIndex = "CREATE " & IIf(idx.Unique = True, "UNIQUE ", "") & "INDEX " & "[" & idx.Name & "]" & vbCrLf & "ON " & tblName & " (" & indxFields & ")"
                End If
                indxFields = ""
            End If
        Next idx
        If GetIndex <> "" Then
            GetIndex = GetIndex & ";"
            'Debug.Print GetIndex
        End If
    End If
    
    End Function
    
    
    Function GetFieldIndex(tblName As String, fldName As String) As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field
     
    Dim strField As String
     
    Set db = CurrentDb()
    Set tdf = db.TableDefs(tblName)
    Set rst = db.OpenRecordset(tblName, dbOpenTable)
     
    
    For Each idx In tdf.Indexes
        If idx.Name = fldName Then
            If idx.Unique = True Then
                GetFieldIndex = " NOT NULL UNIQUE"
            Else
                GetFieldIndex = " NOT NULL"
            End If
            Exit For
        End If
    Next idx
    
    End Function
    Thanks,
    Brad

    Thursday, October 5, 2017 7:48 PM
  • If a field is indexed, that means it is included in an Index in the tabledef's Indexes collection.  This is made more complicated by the fact the the same field may be indexed multiple times, sometimes allowing duplicates, sometimes not.  Access determines what to display in a table's property sheet based on the most restrictive index.  Also, a field may participate in compound indexes that include multiple fields, but that won't count for whether or not the field is indexed by itself.

    I believe the function below, which I just threw together, might serve your purpose:

    Function HowIsFieldIndexed(tableToCheck As Variant, fieldName As String) As String
    
        ' Return one of the following, depending on whether and how the given field
        ' is indexed in the table:
        '
        '   "Yes (No Duplicates)"
        '   "Yes (Duplicates OK)"
        '   "No"
        '
        ' Arguments:
        '
        '   tableToCheck - this may be either the (string) name of the table to look in,
        '                  or a DAO TableDef object representing that table.
        '
        '   fieldName    - the (string) name of the field.
        '
        ' Note: if the field doesn't exist in the table at all, this function will just
        '   return "No", meaning the field is not indexed, and won't raise an error.
        '   It could easily be modified to verify that the field exists in the table.
    
        Dim db          As DAO.Database
        Dim tdf         As DAO.TableDef
        Dim ix          As DAO.Index
        Dim isIndexed   As Boolean
        Dim isUnique    As Boolean
        
        Select Case TypeName(tableToCheck)
            Case "TableDef"
                Set tdf = tableToCheck
            Case "String"
                Set db = CurrentDb
                Set tdf = db.TableDefs(tableToCheck)
            Case Else
                Err.Raise 5
        End Select
    
        With tdf
        
            For Each ix In tdf.Indexes
                If ix.Fields.Count = 1 And ix.Fields(0).name = fieldName Then
                    isIndexed = True
                    If ix.Unique Then
                        isUnique = True
                    End If
                End If
            Next ix
            
        End With
    
        If isIndexed Then
            If isUnique Then
                HowIsFieldIndexed = "Yes (No Duplicates)"
            Else
                HowIsFieldIndexed = "Yes (Duplicates OK)"
            End If
        Else
            HowIsFieldIndexed = "No"
        End If
        
        Set tdf = Nothing
        Set db = Nothing
    
    End Function
    
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Chenchen Li Wednesday, October 11, 2017 7:04 AM
    Thursday, October 5, 2017 8:03 PM
  • Hi Brad,

    Thanks for sharing the solution.

    If there is no other issues, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    Regards,

    Tony


    Help each other

    Monday, October 16, 2017 7:22 AM