Access Database Table and Query Names to Excel - add fields and table references

[stapuff]

I have been using this code below for sometime and do not take credit for writing it myself. Just do not remember where I got it or original author.

Anyways....the code goes to a specific database and creates a list of tables and queries in the .mdb in cells A2&B2 going down.

I want to add additional information goes horizontal for each table or query. For tables I would like the list of field names and for queries I would like the list of field names and tables associated.

I thought I had done this several years ago, but I can not see my older posts and I can not find the original spreadsheet. Any thoughts or suggestions would be greatly appreciated.

Kurt

Option Explicit
Public Sub Example()
EnumerateDBTables Range("A1:A2"), "S:\Production Control\Warehouse_Database\Query_Master.mdb"
End Sub


Public Sub EnumerateDBTables(ByVal target As Excel.Range, ByVal dbPath As _
String, Optional ByVal clearSheet As Boolean = True)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim ws As Excel.Worksheet
Dim lngRow As Long
Dim lngCol As Long
Dim i As Long
Set ws = target.Parent
If clearSheet Then ws.UsedRange.Clear
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
dbPath
ws.Range(target.Cells(1, 1), target.Cells(1, 1).Offset(0, 1)) = _
Array("Name", "Type")
lngRow = target.Row + 1&
For Each tbl In cat.Tables
If tbl.Type <> "ACCESS TABLE" And tbl.Type <> "SYSTEM TABLE" Then
lngCol = target.Column
ws.Cells(lngRow, lngCol) = tbl.Name
ws.Cells(lngRow, lngCol + 1&) = tbl.Type

'* add loop here

lngRow = lngRow + 1&
End If
Next
ws.UsedRange.Columns.AutoFit
End Sub

 

[Norie]

Kurt

All types of queries.

I found the problem though, the mdb database I tried was just queries with no data.

I create a new one, imported some data in, create some queries etc and it the code does return queries.

So my fault I suppose.

 

[stapuff]

cool...any thoughts on how to drill down to the table names of a query?

Kurt

 

[Norie]

Kurt

I'm afraid not.

When I was running the code and checking the catalogue in the watch window I couldn't see anything that would give you the name of the table the column comes from.

Maybe I should try it with a more complicated query.

 

[stapuff]

Norie -

I got the same results as you. Not sure how to get it, but it has to available by some method...maybe just not this one.

Kurt