How to Access VBA Data Types

Get field data type in Access using VBA.

Knowing how to determine the data type of a particular field in a table can make your database application more dynamic. Microsoft Office Access is a relational database management system included in the Microsoft Office suite often used to design local databases. A query is used to retrieve data from a database table. Visual Basic for Applications, or VBA, is a computer programming language used to automate routine tasks in Access and other Microsoft Office applications.

Things You'll Need

• Microsoft Office Access
• Northwind Database

Instructions

1. Open the Northwind Microsoft Office Access database. The Northwind database is included in the Microsoft Office suite. Select the "Insert" menu and click "Module."

2. Copy and paste the following into your new module to loop through the fields of the "Employee" table and display their data types:

   Private Sub getDataTypes()

   Dim varNum As Variant

   Dim rst As Recordset

   Dim dbs As Database

   Dim fldCnt As Integer

   Set dbs = CurrentDb

   strSQL = "SELECT Employees.* FROM Employees;"

   Set rst = dbs.OpenRecordset(strSQL)

   For fldCnt = 0 To rst.Fields.Count - 1

   varNum = rst.Fields(fldCnt).Type

   Select Case varNum

   Case Is = dbBigInt

   Debug.Print "Data Type is Big Integer"

   Case Is = dbBinary

   Debug.Print "Data Type is Binary"

   Case Is = dbBoolean

   Debug.Print "Data Type is Boolean"

   Case Is = dbByte

   Debug.Print "Data Type is Byte"

   Case Is = dbChar

   Debug.Print "Data Type is Char"

   Case Is = dbCurrency

   Debug.Print "Data Type is Currency"

   Case Is = dbDate

   Debug.Print "Data Type is Date / Time"

   Case Is = dbDecimal

   Debug.Print "Data Type is Decimal"

   Case Is = dbDouble

   Debug.Print "Data Type is Double"

   Case Is = dbFloat

   Debug.Print "Data Type is Float"

   Case Is = dbGUID

   Debug.Print "Data Type is Guid"

   Case Is = dbInteger

   Debug.Print "Data Type is Integer"

   Case Is = dbLong

   Debug.Print "Data Type is Long"

   Case Is = dbLongBinary

   Debug.Print "Data Type is Long Binary (OLE Object)"

   Case Is = dbMemo

   Debug.Print "Data Type is Memo"

   Case Is = dbNumeric

   Debug.Print "Data Type is Numeric"

   Case Is = dbSingle

   Debug.Print "Data Type is Single"

   Case Is = dbText

   Debug.Print "Data Type is Text"

   Case Is = dbTime

   Debug.Print "Data Type is Time"

   Case Is = dbTimeStamp

   Debug.Print "Data Type is Time Stamp"

   Case Is = dbVarBinary

   Debug.Print "Data Type is VarBinary"

   End Select

   Next fldCnt

   End Sub

3. Press "F5" to run your subroutine. The field data types for the "Employee" table will be displayed through the Immediate Window.

References

• MSDN Microsoft: Microsoft Access Constants - Overview [Access 2003 VBA Language Reference]