Access VBA Get Field Names

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

The first step would be to get the name of the table. The article below explains how you can get the name of all the tables in an access database:


Get Field Names:

In the example below it is assumed the database has a table with the name “MyTable1”:

Fields

Note: In order to work with the Recordset object you may need to add reference to the Microsoft ActiveX Data Object Library. This has been explained in the article below:

Sub Example()
Dim objRecordset As ADODB.Recordset
Dim arrStrings(1 To 100) As String
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
arrStrings(intIndex) = objRecordset.Fields.Item(i).Name
intIndex = intIndex + 1
Next i
End Sub

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:

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 *