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

You can probably figure out how to revise your ADOX approach to get what you want. However, since you can open a working ADO connection to the target db, I think it should be simpler to just use that connection to execute an ALTER TABLE statement which adds the autonumber MsgID field to your table.

I tested this approach in Access 2007 and it successfully added the autonumber field to my tblMsg table. (However I had to alter the ADO connection properties in order to connect to my db file.)

Dim cnn As Object
Dim DataSource As String
Dim strSql 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; "
strSql = "ALTER TABLE tblMsg ADD COLUMN MsgID COUNTER PRIMARY KEY;"
Debug.Print strSql
cnn.Execute strSql

Answer 2

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.