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
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