Answered by:
Determine if a field is Indexed

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,
BradThursday, 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
- Proposed as answer by Edward8520Microsoft contingent staff Monday, October 16, 2017 7:16 AM
- Marked as answer by mbrad Monday, October 16, 2017 12:11 PM
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 SubRegards, Hans Vogelaar (http://www.eileenslounge.com)
- Proposed as answer by Edward8520Microsoft contingent staff Monday, October 16, 2017 7:16 AM
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
- Proposed as answer by Edward8520Microsoft contingent staff Monday, October 16, 2017 7:16 AM
- Marked as answer by mbrad Monday, October 16, 2017 12:11 PM
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