Jet (MS Access) Get seed and increment of COUNTER column

Question

Does anyone know how to retrieve the seed and increment of an auto increment field (aka COUNTER, or "AutoNumber" in the Access UI) in a Jet (MS Access) database?

I have enumerated all properties of the corresponding DAO.Field object, ADO.Field object, and ADOX.Column object, and have not been able to identify anything. Any method is acceptable, including whacky hacks of the MSys* tables or native method calls.

Background:

In Jet SQL, you can create an auto-incrementing column with a custom seed and increment with the DDL data type clause COUNTER(seed, increment), as in:

CREATE TABLE ODD_INCREMENTER (
    ID_COL   COUNTER(-52098, 42)
  , TEXT_COL VARCHAR(50)
)

Which creates the following table (some data added for demonstration):

enter image description here

Have you looked at this? http://stackoverflow.com/questions/6498221/retreve-next-autonumber-for-access-table — HK1, Oct 24 '12 at 20:52

Fionnuala · Accepted Answer · 2012-10-24T21:11:38.773

3

You can use ADOX

Dim cat As New ADOX.Catalog 
Dim tbl As ADOX.Table       
Dim col As ADOX.Column      

Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("Table1")
Set col = tbl.Columns("AKey")
'Next autonumber
lngSeed = col.Properties("Seed")

Allen Browne has a fairly detailed reference: http://allenbrowne.com/ser-40.html

edited Oct 24 '12 at 21:11

answered Oct 24 '12 at 20:54

Fionnuala

90,370
7
114
152

1

Aaah, I was using `CurrentProject.AccessConnection` – Joshua Honig Oct 24 '12 at 20:55

score 2 · Answer 2 · answered Oct 24 '12 at 21:02

Consider using late binding instead of adding a reference.

You can get the increment in addition to the seed by inspecting its property.

Public Sub SeedAndIncrement(ByVal pTable As String, _
        ByVal pAutonumField As String)

    Dim cat As Object
    Dim objColumn As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    Set objColumn = cat.Tables(pTable).Columns(pAutonumField)
    Debug.Print "Seed: " & objColumn.Properties("Seed")
    Debug.Print "Increment: " & objColumn.Properties("Increment")
    Set objColumn = Nothing
    Set cat = Nothing
End Sub

Jet (MS Access) Get seed and increment of COUNTER column

2 Answers2