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)


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.