Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Determine Field Type
From Wiki
It is possible to determine the field type of the ControlSource of a control using SourceTable and SourceField properties of the field with the form's recordset.
Example: Field Type from Form's Recordset
'You will need ... 'An open form ... Set frm = Forms!frmForm 'A textbox with a control source Set fld = frm.Recordset.Fields(frm.txtText.ControlSource) Debug.Print FieldTypeX(CurrentDb.TableDefs(fld.SourceTable).Fields(fld.SourceField).Type)
You can get field types from any recordset.
Example: Field Type from Recordset
Sub ListTypesRS()
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
'An SQL string
strSQL = "Select * From tblSampleFields"
'Open a recordset using the SQL string.
Set rs = db.OpenRecordset(strSQL)
'Iterate through all the fields ...
For Each fld In rs.Fields
'And output to the immediate window (ctl + G to view)
Debug.Print fld.Name & " - " & FieldTypeX(fld.Type)
Next
'Output only one field
Debug.Print rs.Fields("aText").Name & " - " _
& FieldTypeX(rs.Fields("aText").Type)
End Sub
The table definition file can be used.
Example: Field Type from Table Definition File
Sub ListTypesTDF()
Dim tdf As TableDef
Dim db As Database
Set db = CurrentDb
'Get the table definition file for the table
Set tdf = db.TableDefs("tblSampleFields")
'Iterate through all the fields ...
For Each fld In tdf.Fields
'And output to the immediate window (ctl + G to view)
Debug.Print fld.Name & " - " & FieldTypeX(fld.Type)
Next
'Output only one field
Debug.Print tdf.Fields("aText").Name _
& " - " & FieldTypeX(tdf.Fields("aText").Type)
'Note that hyperlink fields return a memo type ...
Debug.Print tdf.Fields("aHyperlink").Name & " - " _
& FieldTypeX(tdf.Fields("aHyperlink").Type)
'You must check the attributes, to see if it is a hyperlink field
Debug.Print tdf.Fields("aHyperlink").Name & " - " _
& IIf(tdf.Fields("aHyperlink").Attributes And dbHyperlinkField, _
"Hyperlink", "Memo")
'Similary, autonumber fields return a long type ...
Debug.Print tdf.Fields("aAuto").Name & " - " _
& FieldTypeX(tdf.Fields("aAuto").Type)
'Again, check the attributes ...
Debug.Print tdf.Fields("aAuto").Name & " - " _
& IIf(tdf.Fields("aAuto").Attributes And dbAutoIncrField, _
"Autonumber", "LongInteger")
End Sub
You can use schemas.
Example: Field Type from ADO and Schemas
Sub ListTypesADO()
Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim connString As String
'Use the current database
Set cn = CurrentProject.Connection
'Use the Columns Schema to get all fields (columns) in the table.
'Array: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "tblSampleFields"))
'Loop through all the fields ...
While Not rs.EOF
'And output to the immediate window (ctl + G to view)
Debug.Print rs!COLUMN_NAME & " - " & FieldTypeY(rs!Data_Type)
rs.MoveNext
Wend
'Use the Columns Schema to get one field (column) in the table.
'Array: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "tblSampleFields", "aText"))
'And output to the immediate window (ctl + G to view)
Debug.Print rs!COLUMN_NAME & " - " & FieldTypeY(rs!Data_Type)
End Sub
You will need some lists for the above examples.
DAO Field Types
Function FieldTypeX(intType As Integer) As String
Select Case intType
Case dbAttachment
FieldTypeX = "dbAttachment"
Case dbBigInt
FieldTypeX = "dbBigInt"
Case dbBinary
FieldTypeX = "dbBinary"
Case dbBoolean
FieldTypeX = "dbBoolean"
Case dbByte
FieldTypeX = "dbByte"
Case dbChar
FieldTypeX = "dbChar"
Case dbComplexByte
FieldTypeX = "dbComplexByte"
Case dbComplexDecimal
FieldTypeX = "dbComplexDecimal"
Case dbComplexDouble
FieldTypeX = "dbComplexDouble"
Case dbComplexGUID
FieldTypeX = "dbComplexGUID"
Case dbComplexInteger
FieldTypeX = "dbComplexInteger"
Case dbComplexLong
FieldTypeX = "dbComplexLong"
Case dbComplexSingle
FieldTypeX = "dbComplexSingle"
Case dbComplexText
FieldTypeX = "dbComplexText"
Case dbCurrency
FieldTypeX = "dbCurrency"
Case dbDate
FieldTypeX = "dbDate"
Case dbDecimal
FieldTypeX = "dbDecimal"
Case dbDouble
FieldTypeX = "dbDouble"
Case dbFloat
FieldTypeX = "dbFloat"
Case dbGUID
FieldTypeX = "dbGUID"
Case dbInteger
FieldTypeX = "dbInteger"
Case dbLong
FieldTypeX = "dbLong"
Case dbLongBinary
FieldTypeX = "dbLongBinary"
Case dbMemo
FieldTypeX = "dbMemo"
Case dbNumeric
FieldTypeX = "dbNumeric"
Case dbSingle
FieldTypeX = "dbSingle"
Case dbText
FieldTypeX = "dbText"
Case dbTime
FieldTypeX = "dbTime"
Case dbTimeStamp
FieldTypeX = "dbTimeStamp"
Case dbVarBinary
FieldTypeX = "dbVarBinary"
End Select
End Function
ADO Field Types
Function FieldTypeY(intType As Integer) As String
'Not all of these are used by Access, but the list is useful
'for other providers.
'Access user interface data type DAO data type ADOX data type
'Yes/No dbBoolean adBoolean
'Number (FieldSize = Byte) dbByte adUnsignedTinyInt
'Currency dbCurrency adCurrency
'Data/Time dbDate adDate
'Number (FieldSize = Decimal) dbDecimal adDecimal
'Number (FieldSize = Double) dbDouble adDouble
'Number or AutoNumber
' (FieldSize = Replication ID)^ dbGUID adGUID
'Number (FieldSize = Integer) dbInteger adSmallInt
'Number or AutoNumber
' (FieldSize = LongInteger) dbLong adInteger
'OLE Object dbLongBinary adLongVarBinary
'Memo dbMemo adLongVarWChar
'Number (FieldSize = Single) dbSingle adSingle
'Text dbText adVarWChar
'Hyperlink dbMemo * adLongVarWChar +
'^ Access user interface automatically sets autonumber to Long Integer
'* plus DAO Attributes property set to dbHyperlinkField
'+ plus ADOX provider-specific Column property set to Jet OLEDB:Hyperlink
'Note that the mappings shown below were tested using Access 2000.
Select Case intType
'Case adArray
' FieldTypeY = "adArray"
Case adBigInt
FieldTypeY = "adBigInt"
Case adBinary
'Access: OLE Object
FieldTypeY = "adBinary"
Case adBoolean
'Access: YesNo
FieldTypeY = "adBoolean"
Case adBSTR
FieldTypeY = "adBSTR"
Case adChapter
FieldTypeY = "adChapter"
Case adChar
FieldTypeY = "adChar "
Case adCurrency
'Access: Currency
FieldTypeY = "adCurrency "
Case adDate
'Access: Date/Time
FieldTypeY = "adDate"
Case adDBDate
FieldTypeY = "adDBDate"
Case adDBFileTime
FieldTypeY = "adDBFileTime "
Case adDBTime
FieldTypeY = "adDBTime"
Case adDBTimeStamp
FieldTypeY = "adDBTimeStamp"
Case adDecimal
FieldTypeY = "adDecimal"
Case adDouble
FieldTypeY = "adDouble "
Case adEmpty
FieldTypeY = "adEmpty "
Case adError
FieldTypeY = "adError"
Case adFileTime
FieldTypeY = "adFileTime "
Case adGUID
'Access: Replication ID
FieldTypeY = "adGUID "
Case adIDispatch
FieldTypeY = "adIDispatch "
Case adInteger
'Access: Autonumber, Double, Long Integer
FieldTypeY = "adInteger "
Case adIUnknown
FieldTypeY = "adIUnknown "
Case adLongVarBinary
FieldTypeY = "adLongVarBinary "
Case adLongVarChar
FieldTypeY = "adLongVarChar"
Case adLongVarWChar
FieldTypeY = "adLongVarWChar "
Case adNumeric
'Access: Decimal
FieldTypeY = "adNumeric "
Case adPropVariant
FieldTypeY = "adPropVariant"
Case adSingle
'Access: Single
FieldTypeY = "adSingle "
Case adSmallInt
'Access: Integer
FieldTypeY = "adSmallInt "
Case adTinyInt
FieldTypeY = "adTinyInt "
Case adUnsignedBigInt
FieldTypeY = "adUnsignedBigInt "
Case adUnsignedInt
FieldTypeY = "adUnsignedInt "
Case adUnsignedSmallInt
FieldTypeY = "adUnsignedSmallInt"
Case adUnsignedTinyInt
'Access: Byte
FieldTypeY = "adUnsignedTinyInt "
Case adUserDefined
FieldTypeY = "adUserDefined"
Case adVarBinary
FieldTypeY = "adVarBinary "
Case adVarChar
FieldTypeY = "adVarChar "
Case adVariant
FieldTypeY = "adVariant "
Case adVarNumeric
FieldTypeY = "adVarNumeric"
Case adVarWChar
FieldTypeY = "adVarWChar "
Case adWChar
'Access: Hyperlink, Memo, Text
FieldTypeY = "adWChar"
End Select
End Function
Futher Information
HOW TO: Use the Attributes Property for TableDef Objects in Access 2000: http://support.microsoft.com/kb/210362
Creating and Modifying Access Tables: http://msdn2.microsoft.com/en-us/library/aa164917(office.10).aspx
ADO OpenSchema Method: http://www.w3schools.com/ado/met_conn_openschema.asp
DataTypeEnum: http://msdn2.microsoft.com/en-us/library/ms675318.aspx
Creating and Modifying Access Tables: http://msdn2.microsoft.com/en-us/library/aa164917(office.10).aspx
DataTypeEnum Enumeration: http://msdn2.microsoft.com/en-us/library/bb242635.aspx
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.