|
|
|
|
|
|
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()
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 dBase = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.workbooks.Add
On Error Resume Next
For lTbl = 0 To dBase.TableDefs.Count
If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _
Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then
Else
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
On Error GoTo 0
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing
Set dBase = Nothing
End Sub
|
How to use:
|
- Copy the above code.
- In the database explorer window, choose "Modules" on the left.
- Click "New" to create a new module.
- Paste the code in the window that opens.
|
Test the code:
|
- While still in the code module, press F5 to run the procedure.
- 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.
|
|