Access

List all Tables and Fields in an Excel spreadsheet

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

This code was developed to quickly build a list of 90 tables and their field names from an ODBC database, and dump it into Excel. 

Discussion:

This code was originally used when our company installed an ODBC link to a database without documentation. Their were over 90 tables with a huge list of felds. Being that it was an ODBC linked database, and not MS compliant, there were no relationships defined, so this had to be done manually. We used the following code to write all the table and field names to a file so that we could read through them more easily. (The built in Documenter gave us far too much information as we just wanted a simpe list.) 

Code:

instructions for use

			

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 End Sub

How to use:

  1. Copy the above code.
  2. In the database explorer window, choose "Modules" on the left.
  3. Click "New" to create a new module.
  4. Paste the code in the window that opens.
 

Test the code:

  1. While still in the code module, press F5 to run the procedure.
  2. You will be taken into a new instance of Excel with your tables and fields listed.
 

Sample File:

ComputerIssues.zip 30.06KB 

Approved by mdmackillop


This entry has been viewed 299 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express