Creating a table in code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glenn Suggs - 30 Jan 2007 16:00 GMT

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,

Signature

Glenn

Reply to this Message

Douglas J. Steele - 30 Jan 2007 16:04 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I'm creating a temporary table in a module and I can't seem to get one of
> the
[quoted text clipped - 29 lines]
> work. Can anyone help?
> Thanks in advance,

Reply to this Message

Glenn Suggs - 30 Jan 2007 16:32 GMT

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,

Signature

Glenn

> Dim fldNew As DAO.Field
>
[quoted text clipped - 58 lines]
> > work. Can anyone help?
> > Thanks in advance,

Reply to this Message