Set Field Length with MsQuery

[rgbanse]

Similar to the format command or using Cdbl(0) to create a Double field, is there a way to set a text field's length using a standard MS Query ie; FieldName: FieldLen = 4

I want to set the length of the field when a MakeTable query is run. The initial query will create a table with some empty text fields that I will update later. There are quite a few of these fields and thay default to a size of 255. This will eventually cause the size of the Db to become too large.
The query would be similar to <FieldName:&quot;SpaceFiller&quot;.>
&quot;SpaceFiller&quot; would be the maximum length of any data updated in the field, therefore, I don't need fields 255 Char's long.
thx
RGB

 

[DougP]

got this out of VBA help

Sub SizeX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim fldNew As Field
Dim fldLoop As Field

Set dbsNorthwind = OpenDatabase(&quot;Northwind.mdb&quot
Set tdfEmployees = dbsNorthwind.TableDefs!Employees

With tdfEmployees

' Create and append a new Field object to the
' Employees table.
Set fldNew = .CreateField(&quot;FaxPhone&quot
fldNew.Type = dbText
fldNew.Size = 20
.Fields.Append fldNew

Debug.Print &quot;TableDef: &quot; & .Name
Debug.Print &quot; Field.Name - Field.Type - Field.Size&quot;

' Enumerate Fields collection; print field names,
' types, and sizes.
For Each fldLoop In .Fields
Debug.Print &quot; &quot; & fldLoop.Name & &quot; - &quot; & _
fldLoop.Type & &quot; - &quot; & fldLoop.Size
Next fldLoop

' Delete new field because this is a demonstration.
.Fields.Delete fldNew.Name

End With

dbsNorthwind.Close

End Sub
DougP, MCP

 

[rgbanse]

Thanks Doug,
Was hoping not to create the tables with Code/Sql but it looks like it's going to be the only way.
thx again
RGB