How to find auto-increment indexes in access database

Question

From visual studio 2022 I need to find in an access database the list of indexes that have an automatic increase.

I tried with oledbconnection.getshema("indexes")

but I did not succeed.

Can anyone help me?

Thank you

Marco Dell'Oca

Answer 1

Hi @Marco Dell'Oca ,

You can use OleDbConnection.GetSchema("Columns") to get the column details and then filter out the AutoNumber fields.

        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourDatabase.accdb;"
        Using connection As New OleDbConnection(connectionString)
            connection.Open()
            
            Dim columnsSchema As DataTable = connection.GetSchema("Columns")

            Dim autoNumberColumns = From row In columnsSchema.AsEnumerable()
                                    Where row.Field(Of String)("COLUMN_DATA_TYPE") = "3" AndAlso row.Field(Of Boolean)("AUTOINCREMENT") = True
                                    Select TableName = row.Field(Of String)("TABLE_NAME"), ColumnName = row.Field(Of String)("COLUMN_NAME")

            For Each column In autoNumberColumns
                Console.WriteLine("Table: {0}, Column: {1}", column.TableName, column.ColumnName)
            Next
        End Using

Best Regards.

Jiachen Li

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.