Set field description via ADOX

[mouac01]

I can't set the field description property with the code below. I get
the error "Item cannot be found in the collection corresponding to the
requested name or ordinal". Any ideas. TIA...

Sub Test()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = New ADOX.Table
With tbl
.Name = g_sTable
'code to get oFlds object not shown
For Each oFld In oFlds.Rows
Set col = New ADOX.Column
col.Name = oFld("FIELDNAME")
'error occurs here
col.Properties("Description").Value = oFld("FIELDTEXT")
.Columns.Append col
Next
End With
cat.Tables.Append tbl
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Sub

[The Frog]

Hi there,

The reason your code hasn't worked is because you need to set the
parentcatalogue property of the column(s) to be the same ADOX
catalogue as the table before you can alter / add any property
definitions. Give this a try:

Sub Test()

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

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

With tbl
.Name = g_sTable

'code to get oFlds object not shown

For Each oFld In oFlds.Rows
.columns.append "Insert Name of Column Here", columntype
here
with tbl.columns("Insert Name of Column Here")
set .ParentCatalog = cat
.Properties("Description") = "This is a test"
.Properties("Autoincrement") = true
' and just keep adding the bits you need
end with
Next
End With

cat.Tables.Append tbl

'cleanup
set tbl = nothing
set cat = nothing

end sub

I just 'aircoded' this and havent had a chance to test the actual code
out so please check it for typo's. Basically you can see how the
properties are added once the .ParentCatalog is set for the column.
Once that is done you should'nt have any troubles.

Cheers

The Frog

[mouac01]

Wow! I just needed 1 line of code to set the column ParentCatalog
like you said. Thanks...