In this article I will explain how you can get the types of all the fields in a table in an access database using VBA.

Get Field Types:

The access database used in this example has a table with the name “MyTable1”:

Note: You may need to add reference to the Microsoft ActiveX Data Object Library. This can be done in the VBA editor (Tools>>Reference):

Note: The version of the library mentioned above installed on my computer might be different than what is installed on your computer. This will not affect the code:

Sub Example2()
Dim objRecordset As ADODB.Recordset
Dim arrInteger(1 To 100) As Integer
Dim intIndex As Integer
Dim i As Integer

Set objRecordset = New ADODB.Recordset
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("MyTable1")
intIndex = 1
'loop through table fields
For i = 0 To objRecordset.Fields.Count - 1
    arrInteger(intIndex) = objRecordset.Fields.Item(i).Type
    intIndex = intIndex + 1
Next i
End Sub


Result:
As you can the field types have been stored in the array arrInteger:

These numbers can then be compared with the ADODB.DataTypeEnum enumeration to figure out exactly what type of data they are. For example:

• The value 3 is an ADODB.DataTypeEnum.adInteger

• The value 202 is an ADODB.DataTypeEnum.adVarWChar


Note: If you do not wish to add reference to the Microsoft ActiveX Data Object Library you could use late binding to initiate the recordset object:

Dim objRecordset As Object
Set objRecordset = CreateObject("ADODB.Recordset")


You can download the file and code used in this article from the link below:

• Get Field Types.Zip

See also:

• Access VBA Get Field Names
• Check If Table Exists Access VBA
• VBA Access  Get Table Names

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com