List Tables and Fields in ODBC Database


Board Regular
Jan 28, 2009
Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I’m trying to build a list of tables and their corresponding fields, hopefully using excel (2003) rather than querying each table manually and saving it.<o:p></o:p>
The following code (from looks like it would do what I want if I had access, which unfortunately I do not.<o:p></o:p>
If anyone could help me tweak this it would be greatly appreciated.<o:p></o:p>
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
'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

The following is the recorded VBA from querying to show Database name, where ZPROD is the DSN database and ABNFIELD is the first table.

<o:p>Thanks in advance SP</o:p>

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I’m afraid I struggle with the connections bit, I’m connecting to iseries via ODBC <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I can't figure out which names I put in the conn.provider part , assuming the DSN is "ZPROD" and the Database is "XXX01".
I could be miles off here as I’m only guessing the database is the XXX01 from the recorded query. Though I know that ZPROD is an ODBC connection I can set up queries through (hope that makes sense).<o:p></o:p>
Any pointers gladly received as I’m struggling to get my head around this.<o:p></o:p>
[FONT=Courier New][FONT=Courier New] Conn.Provider = [COLOR=brown]"MSDASQL"[/COLOR]<o:p></o:p>[/FONT]
[FONT=Courier New] Conn.Open [COLOR=brown]"DSN=...;Database=...;"[/COLOR], [COLOR=brown]"UID"[/COLOR], [COLOR=brown]"PWD"[/COLOR][/FONT]
Upvote 0
Thank you so much for pointing me in the right direction.
I'm still a long way from understanding how to define connections and things, but the following code seemed to work. This was so much quicker than trying to do a query on each table.
Now all I have to do is figure out what all the field names mean.

Sub ListTablesADO()
  Dim Conn As New ADODB.Connection
  Dim TablesSchema As ADODB.Recordset
  Dim ColumnsSchema As ADODB.Recordset
  Dim R As Long
Conn.Open "Driver={Client Access ODBC Driver (32-bit)};System=XXX01;Uid=YYYY;Pwd=password;Library=LLLL;"
  'Get all database tables.
  R = 1
  Set TablesSchema = Conn.OpenSchema(adSchemaTables)
  Do While Not TablesSchema.EOF
    'Get all table columns.
    Set ColumnsSchema = Conn.OpenSchema(adSchemaColumns, _
      Array(Empty, Empty, "" & TablesSchema("TABLE_NAME")))
    Do While Not ColumnsSchema.EOF
        Worksheets("Sheet1").Range("A" & R) = TablesSchema("TABLE_NAME")
        Worksheets("Sheet1").Range("B" & R) = ColumnsSchema("COLUMN_NAME")
        R = R + 1
      Debug.Print TablesSchema("TABLE_NAME") & ", " & _
End Sub
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back