How to find duplicates OK indexes in Access application?-CodePudding

I have this code that lists all indexes, but I only need those where Indexed: "Yes, duplicates OK" is set. Is there any way to do that instead of manually look through all indexes? I need to migrate the data to SQL Server, but with this I only get empty tables in SQL Server.

Const adSchemaIndexes As Long = 12
Dim cn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim i As Long

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaIndexes)
With rs
    ' enable next three lines to view all the recordset column names
'    For i = 0 To (.Fields.Count - 1)
'        Debug.Print .Fields(i).Name
'    Next i
    Do While Not .EOF
       Debug.Print !TABLE_NAME, !INDEX_NAME, !PRIMARY_KEY
       .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set cn = Nothing

CodePudding user response：

As written, that procedure displays only 3 of the recordset's 25 columns. If you follow the comment instruction to "enable next three lines", you can view the names of all the available columns. One column in particular (UNIQUE) should be useful for your purpose. When you choose "Yes (Duplicates OK)" for the "Indexed" property in table design, it will be displayed as False in the UNIQUE column of the schema recordset.

I assumed you're not interested in information for indexes on system tables ("MSys*" table names). And, for the non-system table indexes, only present information for those without a unique constraint. Here is how I modified the Do While loop accordingly:

Do While Not .EOF
    If !Unique = False And Not !TABLE_NAME Like "MSys*" Then
        Debug.Print !TABLE_NAME, !INDEX_NAME, !COLUMN_NAME, !Unique
   End If
   .MoveNext
Loop

Here is the output from the revised procedure in my test database:

DiscardMe     compound_4_5  f4            False
DiscardMe     compound_4_5  f5            False
DiscardMe     f3            f3            False

That table has 2 non-unique indexes. One of them is a compound index, based on 2 fields. So the schema recordset includes separate rows for each of those fields. The other index is based on a single field, so is presented as a single row in the recordset.

I think that gives you what your question asks for. But I have no idea how those indexes would interfere with migrating your table data to SQL Server. Good luck.