CODEWITHSUNDEEP
ms-access-2007
James Elwell
James Elwell

Reputation: 11

Query to return field names in a table within Microsoft Access

I thought there was a way you can do this in ms access whereby I can run a query on a table called 'Employees' for example and it will return the field names i.e. EmpID, FirstName, Surname titles,not the values of the field e.g. 13, john, doe.

Is this possible in ms access?

Regards,

forestgump

Upvotes: 1

Views: 4068

Answers (2)

Rod
Rod

Reputation: 11

If you just want a list, open the table, select the first line, copy it and paste it into Excel. The field names and the first record will be pasted. Delete the first record. Copy the row with the field names and paste, transpose to get a list. Obviously not usable if you need the info dynamically but helpful if you need a list to work with.

Upvotes: 1

PaulFrancis
PaulFrancis

Reputation: 5819

You will be needing a Recordset object to play with this. Not sure how you intent to use. But this code will print it to the immediate window.

For more info on how to use Recordset - http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners

Public Sub printFieldName(inputTable As String)
    'Takes a TableName as input and prints the field names
    '       of that Table.
    Dim tmpRS As DAO.Recordset
    Dim fCtr As Long

    Set tmpRS = CurrentDB.OpenRecordset("SELECT * FROM " & inputTable)

    For fCtr = 0 To tmpRS.Fields.Count - 1
        Debug.Print tmpRS.Fields(fCtr).Name
    Next

    Set tmpRS = Nothing
End Sub

Usage would be,

printFieldName "TransactionTable"
FirstName
LastName
manTeam
probEnd
department

Upvotes: 3

Related Questions