Creating a table in code

[Guest]

I'm creating a temporary table in a module and I can't seem to get one of the
fields to "AllowZeroLength". I looked at the Help screens and got this much:

' Create a new TableDef object.
Set tdfNew = CurrentDb.CreateTableDef("tblWorkOrdersScheduledPrint")
300
With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' database.
.Fields.Append .CreateField("Network", dbText)
.Fields.Append .CreateField("Location", dbText)
.Fields.Append .CreateField("Type", dbText)
.Fields.Append .CreateField("WO_NBR", dbText)
.Fields.Append .CreateField("MaintReq", dbLong)
.Fields.Append .CreateField("Selection", dbLong)
.Fields.Append .CreateField("WorkOrderType", dbText)
.Fields.Append .CreateField("SerialNumber", dbText)
400
' Append the new TableDef object to the database.
CurrentDb.TableDefs.Append tdfNew

End With
9999
CreateScheduledToPrintExit:
Set tdfNew = Nothing

I want the SerialNumber field to AllowZeroLength but can't seem to make it
work. Can anyone help?
Thanks in advance,

 

[Douglas J. Steele]

Dim fldNew As DAO.Field

' Create a new TableDef object.
Set tdfNew = CurrentDb.CreateTableDef("tblWorkOrdersScheduledPrint")
300
With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' database.
.Fields.Append .CreateField("Network", dbText)
.Fields.Append .CreateField("Location", dbText)
.Fields.Append .CreateField("Type", dbText)
.Fields.Append .CreateField("WO_NBR", dbText)
.Fields.Append .CreateField("MaintReq", dbLong)
.Fields.Append .CreateField("Selection", dbLong)
.Fields.Append .CreateField("WorkOrderType", dbText)
Set fldNew = .CreateField("SerialNumber", dbText)
fldNew.AllowZeroLength = True
tdfNew.Fields.Append fldNew
400
' Append the new TableDef object to the database.
CurrentDb.TableDefs.Append tdfNew

End With
9999
CreateScheduledToPrintExit:
Set tdfNew = Nothing

 

[Guest]

Thanks Douglas,
That worked very well. You know, I saw something very similar in the help
screens but the example had left off the DAO. prefix on the declaration of
the temp field. So it didn't work that way of course.
Thanks again,