Ms-access – How to check with SQL whether a column is an identity column for an Access database

auto-incrementidentityms access

Knowing how to check whether a column is an identity column for Microsoft SQL Server, I currently still found no solution for doing the same with SQL for a Microsoft Access database.

My question:

How can I check with SQL whether a column is an identity column for a Microsoft Access database?

(Since I want to do it in my code, an ADO.NET solution would be helpful, too, although it seems, that this is not possible)

Best Answer

There is a solution with DAO:

Dim db As DAO.Database, tbl As DAO.TableDef
Dim f As DAO.Field
Set db = CurrentDb()

For Each tbl In db.TableDefs
    If tbl.Name = "Table1" Then
        For Each f In tbl.Fields
            If f.Attributes = 17 And f.Type = 4 Then    '(17 = dbAutoIncrField (16) + dbFixedField(1),  4 = dbLong)
                MsgBox ("Table1 idenity column: " & f.Name)
            End If
        Next f
    End If
Next tbl

This checks, if there is a column with Type = 4 [dbLong] and an Attributes value of 17 (16 [dbAutoIncrField] + 1 [dbFixedField]).

This worked fine for me so far.

Related Question