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

 

[stapuff]

well...solved part of the issue. The following will return all the Tables/Queries in a .mbd plus all the column names for each.

2 open issues:

The code below returns the column names in alphabetical order not in the order they actually are in the table/query.
Still looking at how to get the table name for each specific column.

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 column As ADOX.column
Dim ws As Excel.Worksheet
Dim lngRow As Long
Dim lngCol As Long
Dim lngCol1 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

lngCol1 = 3
For Each column In tbl.Columns
ws.Cells(lngRow, lngCol1) = column.Name
lngCol1 = lngCol1 + 1
Next

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

 

[Norie]

Kurt

Aren't you already getting the table name for each column when you loop through the tables?

Or do you mean the table names for the fields in the queries?

 

[stapuff]

Hey Norie -

Yes I was referring to the table names for the fields in the queries.

Thanks,


Kurt

 

[Norie]

Kurt

That code doesn't seem to return queries.

 

[stapuff]

?

Mine does under column B type would be called "VIEW".

Kurt

 

[Norie]

Kurt

I tried it on a mdb database which only had queries, it returned nothing.

I'm using Access 2010, wonder if that makes a difference?

It shouldn't really because the code is accessing the database using ADO.

 

[stapuff]

That is odd.

I have tables and queries and the only type of query it doesn't return is Union. Are your queries "select" queries?

Kurt

 

[stapuff]

I am using Access 2003.

Kurt

 

[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.