I'm trying to copy the structure of an existing table "tblTemplateTable" to a new table "tblNewTable" via VBA and ADOX in an Access database.

Copying the columns works fine, but when I try to copy the Access-specific properties I get a runtime error while trying to append the new table. I spent some time on this but I'm stuck.

Any ideas how to fix the problem?


Private Sub CreateTableFromTemplate()
Dim cat As ADOX.Catalog
Dim colTemplate As ADOX.Column
Dim colNew As ADOX.Column
Dim tblTemplate As ADOX.Table
Dim tblNew As ADOX.Table
Dim propTemplate As ADOX.Property

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

On Error Resume Next
cat.Tables.Delete ("tblNewTable")
On Error GoTo 0

Set tblNew = New ADOX.Table
tblNew.Name = "tblNewTable"

Set tblTemplate = cat.Tables("tblTemplateTable")

'create the fields
For Each colTemplate In tblTemplate.Columns
Set colNew = New ADOX.Column
With colNew
.Name = colTemplate.Name
.Type = colTemplate.Type
.DefinedSize = colTemplate.DefinedSize
End With
tblNew.Columns.Append colNew

'copy the properties of the fields
For Each colTemplate In tblTemplate.Columns
Set colNew = tblNew.Columns(colTemplate.Name)
colNew.ParentCatalog = cat
For Each propTemplate In colTemplate.Properties
colNew.Properties(propTemplate.Name) = propTemplate.Value
Next propTemplate
Next colTemplate

'append the new table
With cat.Tables
.Append tblNew ' <-- at this point the error message pops up :-(
End With

End Sub