Fill a listbox with tables & fields 1

[MisterC]

If I establish an ADO connection to an Access database, how do I fill a listbox with all the db's tables and fields?

 

[MisterC]

Surely someone can help me with this...
I know I can use the alltables collection to get the table names, but I also need to get the tables' field names as well. X-)

 

[petermeachem]

Guess what I did yesterday!
As you are a technical sort of chap I'll just paste my stuff in. It's not hard anyway.

Here we go

This finds the table names

Set rs = cnnnew.OpenSchema(adSchemaTables)
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF
If UCase$(rs(3)) = "TABLE" Then
lstTables.AddItem rs(2)
End If
rs.MoveNext
Loop
End If


This finds the columns for table addresses

Set rs = cnnBasic.OpenSchema(adSchemaColumns)
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF
If UCase$(rs(2)) = "ADDRESSES" Then
TListDestination.AddItem rs(3)
TListDestination.Indent(TListDestination.ListCount - 1) = 0
End If
rs.MoveNext
Loop
End If


I can't get it work that well for Excel tho. It seems to lump columns and sheets in together.


Peter Meachem
peter@accuflight.com

 

[MisterC]

Thanks for the help! --------------
A little knowledge is a dangerous thing.