Adding an Access table with an AutoNumber Primary Key via ADOX

Question

I am trying to add a new table with a primary key and want to set its 'AutoIncrement' property to True. Here is what I am doing:

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim col As New ADOX.Column
Dim cnn As Object
Dim dbs As Database
Dim DataSource As String

DataSource = "\\spdb\depts\Msg_be.accdb"

Set cnn = CreateObject("ADODB.Connection")

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
  "Data Source=" & DataSource & ";Jet OLEDB:Database Password=psWrD; "

Set dbs = OpenDatabase(DataSource, False, False, "MS Access;PWD=psWrD")
cat.ActiveConnection = cnn

tbl.Name = "tblMsg"

tbl.Columns.Append "MsgID", adInteger
tbl.Keys.Append "PrimaryKey", adKeyPrimary, "MsgID"
tbl.Columns.Item("MsgID").Properties("AutoIncrement") = True
cat.Tables.Append tbl

However, I get this error:

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

at line:

tbl.Columns.Item("MsgID").Properties("AutoIncrement") = True

Am I missing something here?

Answer 1

The following code, borrowing heavily from here, seems to do the trick:

Dim con As ADODB.Connection
Dim cat As ADOX.Catalog, tbl As ADOX.Table
Dim col As ADOX.Column, key As ADOX.key

Set con = New ADODB.Connection
con.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\Public\Database1.accdb;"
Set cat = New ADOX.Catalog
cat.ActiveConnection = con

Set tbl = New ADOX.Table
tbl.Name = "tblMsg"
cat.Tables.Append tbl

Set col = New ADOX.Column
Set col.ParentCatalog = cat
col.Name = "MsgID"
col.Type = adInteger
col.Properties("AutoIncrement") = True
col.Properties("Seed") = CLng(1)
col.Properties("Increment") = CLng(1)
tbl.Columns.Append col

Set key = New ADOX.key
key.Name = "PRIMARY"
key.Type = adKeyPrimary
key.Columns.Append "MsgID"
tbl.Keys.Append key

Key points:

• The "AutoNumber-ness" is a function of the column (field), not the key.
• Setting the .ParentCatalog object property allows ADOX to recognize that col is an Access column and therefore can have "AutoIncrement", "Seed", and "Increment" Properties, all three of which combine to form an AutoNumber field.