How to easily get a list of field names in MS Access



If you have a MS Access table that has 100 fields (also known as columns), what is the easiest way to get a list of all the field names? One option is to use Database Documenter.

Access database documenter is a database tool used to document the various database objects such as tables, queries, forms, reports, macros, and so on.

Database Documenter:

Here is what you can document for a table object:

In regards to the problem we're going to solve, the documenter is a bit overkill because, in addition to the field names, the documenter also exports data types and field sizes in the file exported and the file is separated into multiple pages if there are too many things that is impractical to fit into one page. What if the only thing we'd like to get is a list of field names and nothing else? I happened to discover a way to easily accomplish this.

Export the list of field names

Suppose I need to get a list of the field names for Employees table in Northwind database. Below is the method I have been using.

Step 1: Open Northwind database. Right click on Employees table and select Export -> Excel.

Step 2: Select a destination folder and file name. Then click OK to export.

Step 3: Open the exported file Exmloyees.xlsx and highlight the first row for the column names.

Step 4: Right click on the first row and select Copy.

Step 5: Right click in cell A13 and select Paste Special.

Step 6: On the Paste Special window, tick Transpose checkbox and click OK.

Step 7: Now the field names in Employees table are pasted in column A as a list.

Even better way for large tables

You may have noticed that the data in Employees table were also exported to Excel spreadsheet. What if the table has half a million rows of data? Will that need to be exported as well? Of course not. There is a way to export only the field names without the data - duplicate Employees table but for structure only and then only export the duplicated table.

Step 1: Open Northwind database. Right click on Employees table and select Copy.

Step 2: Right click and select Paste.

Step 3: Paste the Employee table and select Structure Only. Name the new table Employees_copy.

Step 4: Export the new table Employees_copy.

Then the rest of the steps are the same as shown before. In the Excel spreadsheet, you'll only see field names and no data is exported because table Employees_copy does not contain any data. This technique is particularly useful for large Access tables.

Happy Exporting!


Copyright© GeeksEngine.com

Other Recent Articles from the MS Access category: 1. Examples of MS Access DateDiff function used in query and VBA code 2. MS Access DateDiff function 3. How to find out your computer name and username by VBA 4. Examples of MS Access DatePart function 5. MS Access DatePart function 6. Examples of MS Access DateAdd function 7. MS Access DateAdd function 8. IIF function basics - the CASE statement of MS Access 9. MS Access Date Expression 10. Solved: MS Access error "The text is too long to be edited" 11. Create MS Access Combo Box essential properties by VBA code 12. Create MS Access Combo Box essential properties manually 13. How to do text search in MS Access programmatically 14. Solved - the size of the Access query result is larger than the maximum size of a database (2 GB) 15. How to count distinct records in MS Access 16. How to do transaction based processing in MS Access 17. How to open a document (local/network file or web page) from MS Access 18. How to use ADOX to create unique composite index - the VBA approach 19. How to do cross-table update queries in MS Access - the right way 20. Three efficient ways to get the number of records by using VBA 21. How to create a composite unique index (not as a primary key) in MS Access 22. Use VBA to get the correct number of records in a Recordset object 23. Disable Access Prompt when a record is changed, table deleted, or action queries run 24. How to hide and unhide a MS Access object 25. How to return multiple values from a VBA function (Part 3) 26. How to return multiple values from a VBA function (Part 2) 27. How to return multiple values from a VBA function (Part 1) 28. Three ways to programmatically duplicate a table in MS Access by VBA 29. Create a DLL by CSharp or VB.Net for VBA 30. How to correctly reference and call a DLL 31. How to register a C# or VB.Net DLL 32. Email address validation by Regular Expressions using VBA 33. Fix MS Access error: Query must have at least one destination field 34. How to unselect radio buttons in MS Access after it has been selected 35. How to Change Query Timeout Value for MS Access SQL Queries 36. What is Northwind Traders database