How To Check Table Field Index Property = Yes (No Duplicates)

[q582gmzhi1]

Hi,

I am trying to find a way to check if an existing Field in a Table, Indexed Property value = Yes (No Duplicates), before running some imports via VBA.

I don't know what happened but noticed the field index Property had changed from Yes (No Duplicates) to Yes (Duplicates OK) and it caused me a slight headache sorting it out.

Just wanted to check before future imports takes place that the property had not changed from Yes (No Duplicates).

Many thanks in advance...

 

[MajP]

Index object - Data Access Objects (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[arnelgp]

you can create a public function in a module, passing the tablename and fieldname to test if it has a unique index:

Public Function IsIndexUnique(strTable As String, strField As String) As Boolean
    
    On Error Resume Next
    
    Dim td As DAO.TableDef
    Dim idx As DAO.Index
    Dim db As DAO.Database
    
    Set db = CurrentDb
    Set td = db.TableDefs(strTable)
    If Err Then
        Set db = Nothing
        Exit Function
    End If
    For Each idx In td.Indexes
        If idx.Fields = "+" & strField Then
            IsIndexUnique = idx.Unique
            Exit For
        End If
    Next
    Set idx = Nothing
    Set td = Nothing
    Set db = Nothing
End Function

 

[ebs17]

I don't know what happened but noticed the field index Property had changed from Yes (No Duplicates) to Yes (Duplicates OK)

If someone else changes the table definitions without qualification, this is a very problematic case. This should be observed and prevented.

Eberhard

 

[The_Doc_Man]

@q582gmzhi1

The question is, if you set it to "No Dups" then how would it get unset? Do you allow folks unrestricted access to your tables? Do a search on "securing a database" to see articles on hiding the ribbon, tables, queries, and other objects. Otherwise you are running in quicksand.

 

[q582gmzhi1]

you can create a public function in a module, passing the tablename and fieldname to test if it has a unique index:

Public Function IsIndexUnique(strTable As String, strField As String) As Boolean
   
    On Error Resume Next
   
    Dim td As DAO.TableDef
    Dim idx As DAO.Index
    Dim db As DAO.Database
   
    Set db = CurrentDb
    Set td = db.TableDefs(strTable)
    If Err Then
        Set db = Nothing
        Exit Function
    End If
    For Each idx In td.Indexes
        If idx.Fields = "+" & strField Then
            IsIndexUnique = idx.Unique
            Exit For
        End If
    Next
    Set idx = Nothing
    Set td = Nothing
    Set db = Nothing
End Function

Thanks works great. Now have several checks in place.

 

[q582gmzhi1]

@q582gmzhi1

The question is, if you set it to "No Dups" then how would it get unset? Do you allow folks unrestricted access to your tables? Do a search on "securing a database" to see articles on hiding the ribbon, tables, queries, and other objects. Otherwise you are running in quicksand.

There is only me that uses the db, I can only think I must have unset it accidentally when doing some tweeks. Now the above code is in place to check I don't accidentally change it again.