The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer. All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. This policy is subject to change at any time. The owner is not an attorney, and nothing posted on this site should be construed as legal advice. Litigation Support Tip of the Night does not provide confirmation that any e-discovery technique or conduct is compliant with legal, regulatory, contractual or ethical requirements.
Featured on the ACEDS blog.
Follow me on Twitter and see How-To Videos on my YouTube channel.
New tips for paralegals and litigation support profesionals are posted to this site each night. Click on the blog headings for better detail.
. . . you can press ALT + F11 to enter Visual Basic. Right click on Modules in the list to the left and insert a new module. Paste in the code and press play.
An Excel file like this will be generated listing the tables in one column and the fields in each table in the second column. Note however that the code has a flaw in it: it omits the first column in each table.
Option Compare Database Option Explicit
Sub ListTablesAndFields() 'Macro Purpose: Write all table and field names to and Excel file
Dim lTbl As Long Dim lFld As Long Dim dBase As Database Dim xlApp As Object Dim wbExcel As Object Dim lRow As Long
'Set current database to a variable adn create a new Excel instance Set dBase = CurrentDb Set xlApp = CreateObject("Excel.Application") Set wbExcel = xlApp.workbooks.Add
'Set on error in case there is no tables On Error Resume Next
'Loop through all tables For lTbl = 0 To dBase.TableDefs.Count 'If the table name is a temporary or system table then ignore it If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _ Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then '~ indicates a temporary table 'MSYS indicates a system level table Else 'Otherwise, loop through each table, writing the table and field names 'to the Excel file For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count -1 lRow = lRow + 1 With wbExcel.sheets(1) .range("A" & lRow) = dBase.TableDefs(lTbl).Name .range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name End With Next lFld End If Next lTbl 'Resume error breaks On Error GoTo 0
'Set Excel to visible and release it from memory xlApp.Visible = True Set xlApp = Nothing Set wbExcel = Nothing
'Release database object from memory Set dBase = Nothing