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

The first step would be to connect to an external database.I’ve explained this in the article below

• VBA, Open Access Database From Excel

Note: The examples in this article will be provided for 2 methods of automation:

1. Late Binding
2. Early binding

In the latter method we add reference to the Access Object Library, before execution. It will run faster and we will have access to the VBA editor intellisense. On the other hand there is always the risk of compatibility issues arising when the program is run on a computer with a different version of Access installed. For more information about early vs late binding please see the link below:

• Microsoft MSDN: Early and Late Binding (Visual Basic)

Note: Although the code in this article was written in the Excel VBA editor, it can be used in any office application with VBA.

Get Field Types, In External Access Database:

The codes below are based on the following assumptions:

1. The access database is located in the path “D:StuffBusinessTemp”.
2. The name of the access database is “NewDB.accdb”.
3. It is assumed the database has a table with the name “MyTable1”.
4. The table has 3 fields with the names, “MyField1”, “MyField2”, “MyField3”
   

Late Binding:

The code below will print the type of all the fields in the table “MyTable1” in column A of the current sheet:

Sub Example1()
'an Access object
Dim objAccess As Object
Dim objRecordset As Object
Dim i As Integer

Set objAccess = CreateObject("Access.Application")
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'get tables data
Set objRecordset = _
objAccess.CurrentProject.Connection.Execute("MyTable1")
'loop through table fields
For i = 0 To objRecordset.Fields.Count - 1
    Cells(i + 1, 1) = objRecordset.Fields.Item(i).Type
Next i
End Sub


Early Binding:

In early binding you will need to add reference to the libraries below. This can be done in the VBA editor (Tools>>Reference):

• Microsoft Access Access Object Library
• Microsoft ADO Ext. for DDL and Security

Note: The versions of the libraries mentioned above installed on my computer might be different than what is installed on your computer. This will not effect the code:

Sub Example2()
'an Access object
Dim objAccess As Access.Application
Dim objRecordset As ADODB.Recordset
Dim i As Integer

Set objAccess = New Access.Application
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'get tables data
Set objRecordset = _
objAccess.CurrentProject.Connection.Execute("MyTable1")
'loop through table fields
For i = 0 To objRecordset.Fields.Count - 1
    Cells(i + 1, 1) = objRecordset.Fields.Item(i).Type
Next i
End Sub


Result:
The field types have been printed in column A:

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


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

• Get Field Types.xlsm

Note: Although the code was written in the Excel VBA Editor, it can be written in any other office application.

See also:

• Get Field Names in External Access Database, VBA Automation
• Check If Table Exists in External Access Database, VBA Automation
• VBA Automation, External Access Database 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