Invalid operaton while appending field property (1 Viewer)

[AlanS]

I'm using the following Access 97 code to create a table from specifications stored in another table. It worked fine, until I added the statement "tdf.Fields.Append fld" inside of the Do While loop, and I can't figure out why. The help screen for the Description property (applicable to Field objects) says "In Visual Basic, to set this property for the first time you must create an application-defined property by using the DAO CreateProperty method." However, the help screen for the CreateProperty method seems to indicate that it is only for creating user-defined properties. How can I set the Description property for a newly created field in a newly created table?

I'd be grateful for any assistance with this.

Private Sub DoOneTable(TName As String)
Dim dbs As Database, rstSpecs As Recordset, tdf As TableDef, fld As Field, prp As Property
Dim SQLString As String
'On Error GoTo Err_DoOneTable
Set dbs = CurrentDb
SQLString = "SELECT * FROM [_FieldSpecs] WHERE [_FieldSpecs].TableName = '"
SQLString = SQLString & TName & "' ORDER BY FieldPosition;"
Set rstSpecs = dbs.OpenRecordset(SQLString)
Set tdf = dbs.CreateTableDef(TName)
With rstSpecs
Do While Not .EOF
Set fld = tdf.CreateField(!FieldName, DescType(!FieldType), !FldSize)
Set prp = fld.CreateProperty("Description", dbText, ";")
fld.Properties.Append prp
If Not IsNull(!RestrictionType) Then fld.Properties!Description = "Restriction=" & !Description
tdf.Fields.Append fld
.MoveNext
Loop
End With
dbs.TableDefs.Append tdf
Exit_DoOneTable:
Set tdf = Nothing
Set rstSpecs = Nothing
Set dbs = Nothing
Exit Sub
Err_DoOneTable:
SaveError Err.Number, Err.Description, "Table = " & rstSpecs!TableName _
& "; Field = " & rstSpecs!FieldName
Resume Next
End Sub

 

[Alexandre]

To say the truth I had never created TableDef objects that way (I use SQL), but after some experimentation this is what I came up with:

Dim db As Database
Dim tdf As TableDef

Set db = DBEngine(0)(0)

'Create a new TableDef object
Set tdf = db.CreateTableDef("Test")

With tdf
'Create and append at least one field to the created TableDef
.Fields.Append .CreateField("ID", dbLong)
'Append the new TableDef object to the TableDefs collection
db.TableDefs.Append tdf

'Create and append a description property, now that the new TableDef is persistent
.Fields("ID").Properties.Append .CreateProperty("Description", dbText, "Whatever")
End With

Set tdf = Nothing
Set db = Nothing

 

[AlanS]

Thank you, Alex! It worked perfectly. I hadn't realized that the tabledef had to be appended to the tabledefs collection before you can append properties to its fields.