>
Size Property
Applies To
Field Object.
Description
Returns a value that indicates the maximum size, in bytes, of a Field object. For Text fields, you can also use it to set the maximum size of data that the Field object can hold. For an object not yet appended to the Fields collection, this property is read/write.
Settings and Return Values
The setting or return value indicates the maximum size of a Field object. The data type is Long. The setting depends on the Type property setting of the Field object and can be one of these integer values.
Type | Size | Description |
|
dbBoolean | 1 | Boolean |
dbByte | 1 | Byte |
Type | Size | Description |
|
dbInteger | 2 | Integer |
dbLong | 4 | Long |
dbCurrency | 8 | Currency |
dbSingle | 4 | Single |
dbDouble | 8 | Double |
dbDate | 8 | Date/Time |
dbText | 1–255 | Text |
dbLongBinary | 0 | Long Binary (OLE Object) |
dbMemo | 0 | Memo |
dbGUID | 16 | GUID |
Remarks
These constants are listed in the Data Access (DAO) object library in the Object Browser.
Use of the Size property depends on the object that contains the Fields collection that the Field object is appended to, as shown in the following table.
Object appended to | Usage |
|
Index | Not supported |
QueryDef | Read-only |
Recordset | Read-only |
Relation | Not supported |
TableDef | Read-only |
When you create a Field object with a data type other than Text, the Type property setting automatically determines the Size property setting, and you don't need to set the Size property. For a Field object with the Text data type, however, Size can be set to any integer up to the maximum text size (for Microsoft Jet databases that maximum is 255). If you do not set the size, the field will be as large as the database allows.
For OLE Object and Memo Field objects, Size is always set to 0. Use the FieldSize method of the Field object to determine the size of the data in a specific record. The maximum size of an OLE Object or Memo field is limited only by your system resources or the maximum size that the database allows.
See Also
Attributes Property, Index Object, QueryDef Object, Type Property.
Specifics (Microsoft Access)
The Size property is equivalent to the FieldSize property in table Design view. You can't set the FieldSize property in Visual Basic; you must use the Size property instead.
In Microsoft Access, the Memo data type field can contain up to 1.2 gigabytes of information. However, Microsoft Access only displays the first 32K of data in a control on a form or report.
Example
This example creates a new Field object with a Text field type, sets its Name property to Fax Phone and its Size property to 20, and appends the new object to the Fields collection of the Employees table in the TableDefs collection of the database.
Function SizeType() As Integer
Dim dbsNorthwind As Database
Dim fldFaxPhone As Field, tdfEmployees As TableDef
' Open database.
Set dbsNorthwind = _
DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Get existing table reference.
Set tdfEmployees = dbsNorthwind.TableDefs("Employees")
' Create Field object.
Set fldFaxPhone = tdfEmployees.CreateField("Fax Phone")
' Set field properties.
fldFaxPhone.Type = dbText
fldFaxPhone.Size = 20
' Append fldFaxPhone to Fields collection.
tdfEmployees.Fields.Append fldFaxPhone
SizeType = True
End Function
You can also use the CreateField method if you provide name, type, and size as arguments.
Set fldFaxPhone = tdfEmployees.CreateField("Fax Phone", dbText, 20)
Example (Microsoft Access)
The following example creates a new Field object and sets its Size and Type properties. The procedure then appends the new object to the Fields collection of the Employees table in the TableDefs collection of the database.
Sub NewField()
Dim dbs As Database, tdf As TableDef
Dim fld As Field
' Return Database object that points to current database.
Set dbs = CurrentDb
' Return TableDef variable pointing to Employees table.
Set tdf = dbs.TableDefs!Employees
' Create Field object.
Set fld = tdf.CreateField("DaysOfVacation")
' Set field properties.
fld.Type = dbText
fld.Size = 20
' Append fld to Fields collection.
tdf.Fields.Append fld
End Sub
You can also use the CreateField method to set the Name, Type, and Size properties if you provide name, type, and size as arguments.
Set fld = tdfEmployees.CreateField("DaysOfVacation", dbText, 20)
Example (Microsoft Excel)
This example copies onto Sheet1 all fields of the Double type from ORDDTAIL.DBF, a dBASE IV table located in the C:\Program Files\Common Files\Microsoft Shared\MSquery folder. (On Windows NT, ORDDTAIL.DBF is located in the \WINDOWS\MSAPPS\MSQUERY folder.)
Const sourceDir = "C:\Program Files\Common Files\Microsoft Shared\" _
& "MSquery"
Dim db As Database, recordsToCopy As Recordset, tDef As Recordset
Dim fieldsToStore(1000), fileName As String
fileName = "ORDDTAIL.DBF"
Set db = Workspaces(0).OpenDatabase(sourceDir, _
False, False, "dBASE IV")
Set tDef = db.OpenRecordset(fileName)
n = 0
Sheets("Sheet1").Activate
For i = 0 To tDef.Fields.Count - 1
If tDef.Fields(i).Type = dbDouble Then
fieldsToStore(n) = tDef.fields(i).Name
n = n + 1
End If
Next
If fieldsToStore(0) = "" Then
MsgBox "There are no number fields in this table."
Exit Sub
End If
For i = 0 To n - 1
records = "SELECT " & "[" & fieldsToStore(i) & "]" _
& " from " & db.Recordsets(fileName).Name & ";"
Set recordsToCopy = db.OpenRecordset(records)
With ActiveSheet.Cells(1, i + 1)
.CopyFromRecordset recordsToCopy
.ColumnWidth = recordsToCopy.fields(0).Size
End With
Next
recordsToCopy.Close
tDef.Close
db.Close