VBA Access Get Field Types

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”:
Fields

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

References

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:
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:

See also:

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *