The Columns of a Table

The Types of Columns

Introduction

To exercise a good level of control over the values that can be entered or stored in a database, you can configure each column to allow some types of value and/or to exclude some other types. This is done by specifying an appropriate type of data for the column. The type of data of a column follows the same approaches we reviewed about data types on variables but there are differences with the names of data types among the various libraries used.

In Microsoft Access, to specify the data type of a column, you have two alternatives, one is clear but the other is not. If you create a table using the Table Wizard, every column you select and add to the Fields In My New Table has a specific data type. While you are still in the Table Wizard, you cannot know clearly the data type of a column and you cannot change the data type of a column. Only with experience will you know that all columns whose name end with ID are numeric, all column that appear as date-oriented are Date based. The columns that appear as Boolean values use a Boolean data type. All columns named Notes use a Memo data type, etc.

Probably the best place to specify a data type in Microsoft Access is with the Design View of a table. To specify the data type of a column, after entering its name under Field Name, click its corresponding box under Data Type and click the arrow of the combo box to display the list of available types:

If you are programmatically creating a table using either Microsoft Access' own library or if using DAO, pass the data type as the second argument to the CreateField() method.

If you are using SQL to create your table, pass the name of the data type as the second factor of the column.

The various data types available are:

If the fields under a column would be used to hold any type of value, including regular text, such a column is treated as string-based. There are various data types you can apply to such a column. The most common data type is Text. This is equivalent to the String data type we reviewed for Microsoft Visual Basic.

If you are programmatically creating your table, using the Microsoft Access Object Library, to apply a string type to a column, you can use DB_Text. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    ' Create a colume named FullName in the Students table
    Set colFullName = tblStudents.CreateField("FullName", DB_Text)
    ' Add the FullName column to the Students table
    tblStudents.Fields.Append colFullName
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

If you are using DAO to create your table and you want the column to hold text values, you can apply the dbText type.

If you are creating your table using SQL, for a column whose value would be string-based, you can apply the TEXT, the CHAR, or the VARCHAR data types. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim strSQL As String
    
    strSQL = "CREATE Table Contracts (FirstName Text, LastName Varchar);"
    DoCmd.RunSQL strSQL
End Sub

Each one of the text, char, or varchar data types would produce the same effect. A column with the Text, the char, or the varchar data type allows any type of value made of any character up to 255 symbols. If you want column to hold longer text and if you are creating the table in the Design View, after specifying its name, set its Data Type to Memo. Like the Text data type, the Memo is used for any type of text, any combination of characters, and symbols. The main difference with the Text data type is that a Memo column allows storing longer text up to 64000 characters.

If you are programmatically creating a column using the Microsoft Access Object Library, if you want it to hold longer text than the Text data type or a regular String would handle, pass its type as DB_MEMO. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colFullName As Object
    Dim colComments As Object

    Set curDatabase = CurrentDb
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    ' Create a colume named FullName in the Students table
    Set colFullName = tblStudents.CreateField("FullName", DB_Text)
    tblStudents.Fields.Append colFullName
    ' Create the Comments column
    Set colComments = tblStudents.CreateField("Comments", DB_MEMO)
    tblStudents.Fields.Append colComments
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

If you are creating the table using DAO and want to use long text values on a column, specify its data type as dbMemo. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim curDatabase As DAO.Database
    Dim tblStudents As DAO.TableDef
    Dim colFullName As DAO.Field
    Dim colAnnualReview As DAO.Field

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")

    Set colFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append colFullName
    
    Set colAnnualReview = tblStudents.CreateField("AnnualReview", dbMemo)
    tblStudents.Fields.Append colAnnualReview
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

If you are using SQL to create your table, you can apply the MEMO, the NOTE, or the LONGTEXT data types to a column that would hold long text.

Boolean Fields

If you want to create a column to hold only values as being true or being false, Yes or No, On or Off, 0 or –1 and if you are creating the table in the Design View, set its data type to Yes/No.

In the Microsoft Access Object Library, if you are programmatically creating the column on a table and the column would be Boolean-based, set its data type as DB_BOOLEAN. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colIsMarried As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colIsMarried = tblEmployees.CreateField("IsMarried", DB_BOOLEAN)
    tblEmployees.Fields.Append colIsMarried
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating a table using DAO and you want a column to hold Boolean values, specify its data type as dbBoolean. Here is an example:

Private Sub cmdTableCreation_Click()
    Dim curDatabase As DAO.Database
    Dim tblStudents As DAO.TableDef
    Dim colFullName As DAO.Field
    Dim colWasTransfered As DAO.Field

    Set curDatabase = CurrentDb
    
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set colFullName = tblStudents.CreateField("FullName", dbText)
    tblStudents.Fields.Append colFullName
    
    Set colWasTransfered = tblStudents.CreateField("WasTransfered", dbBoolean)
    tblStudents.Fields.Append colWasTransfered
    
    curDatabase.TableDefs.Append tblStudents
End Sub

If you are using SQL to create the column, set its data type as YESNO, BIT, or LOGICAL. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "FullName TEXT, " & _
                 "AvailableOnWeekend BIT, " & _
                 "OwnsACar LOGICAL, " & _
                 "CanShareOwnCar YESNO);"
End Sub

These are equivalent to Microsoft Access' Yes/No and to Microsoft Visual Basic's Boolean data type.

Integral Numeric Fields

In Lesson 3, we introduced the various types of integral values available in Microsoft Visual Basic. When creating a table in the Design View of Microsoft Access, if you want a column to hold natural numbers, first set its data type to Number. In the lower section of the window, click the arrow of the Field Size property and select from the list:

Byte: The Byte data type is the same as we reviewed with variables in Lesson 3. It is made for small numeric value not more than 255. If you are programmatically creating the table and you want the column to hold these types of numbers, set its data type to DB_BYTE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDepartmentCode As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDepartmentCode = tblEmployees.CreateField("DepartmentCode", DB_BYTE)
    tblEmployees.Fields.Append colDepartmentCode
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating the table using DAO and you want a column that would hold small numeric values, you can specify its data type as dbByte.

If you are using SQL to create the column, set its data type to either Byte or Integer1.

Integer: As mentioned in Lesson 3, if you want to use values higher than the Byte is made for, you can use the Integer type. To apply to a column you are creating the Design View of a table, after setting its Data Type to Number in the top section of the table, in the lower section select Integer as its Field Size.

If you are programmatically the column and you want this type of numeric value, set its data type to DB_INTEGER. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDepartmentCode As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDepartmentCode = tblEmployees.CreateField("DepartmentCode", DB_INTEGER)
    tblEmployees.Fields.Append colDepartmentCode
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are using DAO, specify the data type of the column as dbInteger. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbThisOne As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    
    ' Specify the database to use
    Set dbThisOne = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbDeja.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbInteger)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName

   ' Add the new table to the database.
   dbThisOne.TableDefs.Append tblEmployees
   dbThisOne.Close
End Sub

If you are creating the column using SQL and you want to use an integer, set its data type to one of the following: SHORT or INTEGER2. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Countries(DiplCode Short, AreaCode Integer2);"
End Sub

Long: Besides the Byte and the integer, another natural number supported in the libraries is called Long or Long Integer. This is used for significantly high numbers. If you are creating a table in the Design View and you want to apply this data type, select it in the Field Size.

If you are programmatically creating the column, you can set its data type to DB_LONG. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDepartmentCode As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDepartmentCode = tblEmployees.CreateField("DepartmentCode", DB_LONG)
    tblEmployees.Fields.Append colDepartmentCode
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are creating your column in DAO, set its data type to dbLong. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbThisOne As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    Dim fldWeeklyHours As DAO.Field
    
    ' Specify the database to use
    Set dbThisOne = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbDeja.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbLong)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName

   ' Add the new table to the database.
   dbThisOne.TableDefs.Append tblEmployees
   dbThisOne.Close
End Sub

If you are creating the column using SQL, set its data type to one of the following: INT, INTEGER, INTEGER4 or Long. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Countries(Area long, Population integer);"
End Sub

Some of these data types would be applied only to Microsoft Access and may not be used in other SQL environments.

Decimal Numeric Fields

Besides natural numbers, a database also ought to support decimal values. We introduced them in Lesson 3 as values that hold a fraction. Because these are primarily considered as numbers, before applying them to a column, if you are creating a table in the Design View, first set its data type to Number and, in the lower section of the window, click the arrow of the Field Size property.

Floating-Point Value With Single Precision: If you want a regular decimal value without regards to precision on the column, set its data type to Single. If you are programmatically creating the column, specify its data type as DB_SINGLE. If you are using DAO, specify the data type of the column as dbSingle. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbThisOne As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeNumber As DAO.Field
    Dim fldFullName As DAO.Field
    Dim fldWeeklyHours As DAO.Field
    
    ' Specify the database to use
    Set dbThisOne = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbDeja.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", dbLong)
    tblEmployees.Fields.Append fldEmployeeNumber
    
    Set fldFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append fldFullName
    
    Set fldWeeklyHours = tblEmployees.CreateField("WeeklyHours", dbSingle)
    tblEmployees.Fields.Append fldWeeklyHours

   ' Add the new table to the database.
   dbThisOne.TableDefs.Append tblEmployees
   dbThisOne.Close
End Sub

If you are using SQL, specify the column's data type as Single.

Floating-Point Value With Double Precision: If the values of a column will require a good level of precision and if you are creating the table in Design View, specify the Field Size of the column as Double after setting its Data Type to Number.

If you are programmatically creating the column, set its data type to DB_DOUBLE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are using DAO to create your table, set the column's data type to dbDouble.

If you are creating the table in SQL, specify the data type of the column as either NUMERIC or DOUBLE to support double-precision values. 

Money and Currency Fields

When creating a table in the Design View, if you want a column to hold monetary values, set its Data Type to Currency. After setting this data type, when a person is using the application, the database would refer to the language set in the Control Panel and the rules in the Currency property page:

For example, if the database is being used in the US, the US dollar and its rules, including the $ symbol, would be applied. If you want to apply different rules to a particular column, after setting its Data Type to Currency, in the lower section of the window, click the arrow of the Field Size property and select the desired option:

If you are programmatically creating the column and you want it to hold monetary values, set its data type to DB_CURRENCY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object
    Dim colHourlySalary As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    Set colHourlySalary = tblEmployees.CreateField("HourlySalary", DB_CURRENCY)
    tblEmployees.Fields.Append colHourlySalary
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are using DAO, specify its data type as dbCurrency.

If you are using SQL, specify its data type as Money or Currency. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "FullName Text, " & _
                 "WeeklyHours Double, " & _
                 "HourlySalary Money);"
End Sub

Both Money and Currency have the same effect in Microsoft Access.

Date and Time Fields

If you are creating a table in the Design View and you want a column to hold date, time, or both date and time values, set its Data Type to Date/Time. If you are programmatically creating column that would hold date/time values, set its data type to DB_DATE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDateHired As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colDateHired = tblEmployees.CreateField("DateHired", DB_DATE)
    tblEmployees.Fields.Append colDateHired
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

If you are programmatically creating the column in DAO, set its data type to dbDate.

If you are creating a SQL statement to create the table, set its column's data type to either the DATE or the DATETIME types. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "FullName Text, " & _
                 "DateHired Date, " & _
                 "DateLastReviewed DateTime);"
End Sub

Both data types have the same effect in Microsoft Access.

Binary Fields

The binary data type can let a field accept any type of data but it is equipped to interpret the value. For example, it can be used to receive hexadecimal numbers. To specify this when programmatically creating a column, specify its data type as DB_BINARY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblBooks As Object
    Dim colShelf As Object

    Set curDatabase = CurrentDb
    Set tblBooks = curDatabase.CreateTableDef("Books")
    
    Set colShelf = tblBooks.CreateField("Shelf", DB_BINARY)
    tblBooks.Fields.Append colShelf
    
    curDatabase.TableDefs.Append tblBooks
End Sub

If you are creating the table in DAO, set the column's data type as dbBinary.

If you are programmatically creating the column in ADO, set its data type as either BINARY or VARBINARY.

Image Fields

If you are creating a column that will hold external documents, such as pictures, formatted (from Microsoft Word for example), or spreadsheet, etc, and if you are working in the table's Design View, set the column's data type to OLE Object.

If you are programmatically creating the table using SQL and if you are creating a column that would hold embedded objects, set its data type to one of the following: IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.

Hyperlink Fields

A hyperlink is a type of string that allows the user to move to a different file when clicked. Use this data type if you want to allow the user to open documents stored in other applications.

Practical Learning: Setting Columns Types