Adding an access table using the AutoNumber primary key through ADOX

Question

I am trying to add a new table with a primary key and want to set its AutoIncrement property to True. That's what I'm 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.

in line:

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

Did I miss something?

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:

• "AutoNumber-ness" is a function of a column (field), not a key.
• .ParentCatalog ADOX , col Access "AutoIncrement", "Seed" "Increment", AutoNumber.

Answer 2

, , ADOX, , . , ADO- db, , ALTER TABLE, MsgID .

Access 2007 tblMsg. ( ADO, db.)

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