Get the name of the columns from a Access table by a Visual Basic

Question

I'm trying to get the names of the table into a list so I can create an SQL table with them.

I was trying to do this with information_scheme just like SQL does, but MS Access doesn't support it.

Answer 1

I'm unsure whether you are looking to obtain the column names per your question title:

Get the name of the columns from a Access table by a Visual Basic

Or the table names per your question content:

I'm trying to get the names of the table into a list so I can create an SQL table with them.

If the former, you can iterate over the Fields collection of the relevant TableDef object and query the Name property for each Field object.

For example, the following function will return an array of the field names held by the table with the supplied name:

Function FieldNames(strTbl As String) As String()
    Dim dbs As DAO.Database
    Dim def As DAO.TableDef
    Dim fld As DAO.Field
    Dim idx As Integer: idx = 0

    Set dbs = CurrentDb
    Set def = dbs.TableDefs(strTbl)

    Dim rtn() As String
    ReDim rtn(0 To def.Fields.Count - 1)

    For Each fld In def.Fields
        rtn(idx) = fld.Name
        idx = idx + 1
    Next fld
    FieldNames = rtn
End Function

Immediate Window (Ctrl+G) example output:

x = FieldNames("YourTable")
?x(0)
Field1
?x(1)
Field2
?x(2)
Field3

---

If instead you are looking to obtain an array of all table names within the database, you could iterate over the TableDefs collection within the database, as demonstrated by the following function:

Function TableNames() As String()
    Dim dbs As DAO.Database
    Dim def As DAO.TableDef
    Dim idx As Integer: idx = 0

    Set dbs = CurrentDb

    Dim rtn() As String
    ReDim rtn(0 To dbs.TableDefs.Count - 1)

    For Each def In dbs.TableDefs
        rtn(idx) = def.Name
        idx = idx + 1
    Next def
    TableNames = rtn
End Function