Adding an Access table with an AutoNumber Primary Key via ADOX
ms-access, ms-access-2007, vba
Solution
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.
Problem
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?