# Solved: Getting Field Names from Tables in MS Access



## jim4004 (Feb 23, 2006)

Of all of the years I've worked with Access, I've never needed to do this; well here I am being asked to get a list of field names from a table. I'm aware of a few ways (set a list box to be field list, and using the db analyzer). I also found some code that doesn't work to read the database fields and dump them into a file (I'll paste to this note - apparantly I'm not running the correct version of ADO to use it).

Any help would be greatly appreciated. It dies on a "User-Defined type not defined" error inside the funtion at "Dim MyDB As New ADOX.Catalog"

I found the code on a developer article, but not sure I'm implementing it correctly. I feel like I'm out of my league on this one, but willing to give it another shot.

Thanks in Advance

Jim


```
Option Base 1
Public Sub Enumerate_Table()
    On Error GoTo ERROR_PROC
    Dim aryFields()
    Dim lngCount As Long
    lngCount = 0
    strInput = InputBox("Please enter the name of the table for which" & vbCrLf & _
            "you wish to list FieldNames & Descriptions." & vbCrLf & vbCrLf & _
            "Output will be placed in tab-delimited text file.", "Table Name Input", "MainTabl")
    If StrPtr(strInput) = 0 Or Len(strInput) = 0 Then
        Exit Sub
    Else
        strSQL = "SELECT * FROM " & strInput
        Dim Adofl As ADODB.Field
        Dim rs As New ADODB.Recordset
        rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        For Each Adofl In rs.Fields
            lngCount = lngCount + 1
            ReDim Preserve aryFields(2, lngCount)
            aryFields(1, lngCount) = Adofl.Name
            aryFields(2, lngCount) = GetFieldDesc_ADO(strInput, Adofl.Name)
        Next
        rs.Close 'recordset closed for next item.
    End If ' Feed array to designated file
    If lngCount > 0 Then
        aFileNum = FreeFile
        Open "C:\Temp\TableStruc.txt" For Output As #aFileNum
        For i = 1 To UBound(aryFields(), 2)
        Print #aFileNum, aryFields(1, i) & vbTab & aryFields(2, i)
        Next
        Close #aFileNum
Final_Results:
        Btn = MsgBox("Table fieldnames with Descriptions stored in:" & vbCrLf & vbCrLf & _
            "C:\Temp\TableStruc.txt" & vbCrLf & vbCrLf & _
            "Do you wish to OPEN using NOTEPAD?", vbOKCancel + vbQuestion, _
            " Table Enumeration")
        If Btn = vbOK Then ' Opening the file in Notepad
            Shell "Notepad.exe" & " " & "C:\Temp\TableStruc.txt", vbMaximizedFocus
        End If
    Else
        MsgBox "Table Not Found!", vbOKOnly + vbExclamation, _
            "Bad Table Name"
    End If
    Exit Sub
ERROR_PROC:
    rs.Close
    MsgBox "Error encountered attempting to enumerate table!"
End Sub
' This function requires a reference to ADO 2.5 Ext. for DDL & Security (or higher)
Function GetFieldDesc_ADO(ByVal MyTableName As String, ByVal MyFieldName As String)
    Dim MyDB As New ADOX.Catalog
    Dim MyTable As ADOX.Table
    Dim MyField As ADOX.Column
    On Error GoTo Err_GetFieldDescription
    MyDB.ActiveConnection = CurrentProject.Connection
    Set MyTable = MyDB.Tables(MyTableName)
    GetFieldDesc_ADO = MyTable.Columns(MyFieldName).Properties("Description")
    Set MyDB = Nothing
Bye_GetFieldDescription:
    Exit Function
Err_GetFieldDescription:
    Beep
    MsgBox Err.Description, vbExclamation
    GetFieldDescription = Null
    Resume Bye_GetFieldDescription
End Function
```


----------



## Jimmy the Hand (Jul 28, 2006)

Here's one method:


```
Sub FieldNames()
    Dim Rst As Recordset
    Dim f As Field
    
    Set Rst = CurrentDb.OpenRecordset("TableName")
    
    For Each f In Rst.Fields
        MsgBox (f.name)
    Next
    Rst.Close
End Sub
```
Cheers,

Jimmy

Edit:
It works for internal tables, only.


----------



## jim4004 (Feb 23, 2006)

Now that's a simple response, thanks! I do need it for linked tables, however. I should probably just use something from the linked source (Oracle and SQL server) for those. I know I can read the table and column names from there.


----------



## Jimmy the Hand (Jul 28, 2006)

Oops...

The Edit part of my last post should be corrected. The real comment is:

_I haven't tried my code with linked tables yet._

But inspired by your response I just tried it with a linked table. By this I mean that I linked in a table from another *Access *database. The code worked with it. Maybe it works with tables from other DBs as well. (Sadly, I have no way to test it now.)

J.


----------



## jim4004 (Feb 23, 2006)

I tested this with tables linked to Oracle 10g database, and it works fine. Thanks again. I'll mark as solved.


----------

