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”:
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
enumeration to figure out exactly what type of data they are. For example:ADODB.DataTypeEnum
- 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