Accessing Description property of Access

[Steve Moore]

The following code in VBA produces a new Access table based on field information which is in an existing table ('data dictionary'). This works fine but I am unable to set the field descriptions of the new table which are also held in the 'data dictionary' table. The help files inform me that I need to use the createproperty() method to do this but so far I have been unsuccessful. Can anyone help?


Sub CreateTable()

Dim dbs As Database, newtable As TableDef, datadic As Recordset, fld As Field

Set dbs = CurrentDb
Set datadic = dbs.OpenRecordset("Data Dictionary")
Set newtable = dbs.CreateTableDef("NewTable")

datadic.MoveFirst
While Not datadic.EOF
If datadic!FieldType = "date" Then
Set fld = newtable.CreateField(datadic!FieldName, dbDate)
ElseIf datadic!FieldType = "number" Then
Set fld = newtable.CreateField(datadic!FieldName, dbInteger)
ElseIf datadic!FieldType = "boolean" Then
Set fld = newtable.CreateField(datadic!FieldName, dbBoolean)
Else
Set fld = newtable.CreateField(datadic!FieldName, dbText, datadic!FieldSize)
End If
newtable.Fields.Append fld
datadic.MoveNext
Wend

dbs.TableDefs.Append newtable
dbs.TableDefs.Refresh

End Sub







Steve Moore