Open schema column

This is all on a button click
I have this half working i can display all tables in data base in combo1 then the bit i cant get to work is to display all columns in that table in to combo2 any ideas searched the web cant find any help on it


VB CODE
_______________________________________________
Set adox_Catalog = New ADOX.Catalog
Set adox_Catalog.ActiveConnection = cnn
For Each adox_Table In adox_Catalog.Tables
If UCase(Left(adox_Table.Name, 4)) <> "MSYS" Then
Combo1.AddItem adox_Table.Name
End If
Next adox_Table

____________________________________________________
END CODE

here's a bit of old code I found where I used OpenSchema with ADO instead of ADOX to do something like what you want.

I'm adding the col info to a recordset but I'm sure you won't have a problem adding it to the listbox/combo.

For more details check out OpenSchema...

VB Code:

Dim rsTmp As New ADODB.Recordset
        
rsTmp.Fields.Append "TABLE", adWChar, 255
rsTmp.Fields.Append "COLUMN", adWChar, 255
rsTmp.Fields.Append "TYPE", adVarChar, 255
rsTmp.Fields.Append "SEQ", adInteger
rsTmp.Open
 
Dim conn As New ADODB.Connection
conn.Open MyConnectionString
        
Set rs = conn.OpenSchema(adSchemaColumns, Array(Empty, Empty, rsTables(TABLE_NAME)))
              
Do While Not rs.EOF
    rsTmp.AddNew Array(rs.Fields(2).Name, rs.Fields(3).Name, rs.Fields(11).Name, rs.Fields(6).Name), _
                 Array(rs.Fields(2).Value, rs.Fields(3).Value, TypeName(rs.Fields(11).Value, rs.Fields(13).Value), _
                 rs.Fields(6).Value)
    rs.MoveNext
Loop

Its nearly there all i want to do is display column names from a table selected in combo1 but all column names form data base are shown in combo 2. I cant think how to do this any ideas are welcome or could some one point me in the right direction thanx
VB CODE
_______________________________________________

'Add all the table names
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
Combo1.AddItem rstSchema!TABLE_NAME
rstSchema.MoveNext
Loop
rstSchema.Close

'Add all the column names
Set rstSchema = cnn1.OpenSchema(adSchemaColumns)
Do Until rstSchema.EOF
Combo2.AddItem rstSchema!COLUMN_NAME
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
___________________________________________________
END CODE

You did not set any criterias. fungi showed a sample with

Set rs = conn.OpenSchema(adSchemaColumns, Array(Empty, Empty, rsTables(TABLE_NAME)))

In your case

Set rstSchema = cnn1.OpenSchema(adSchemaColumns, Array(Empty,Empty, Combo1.Text) )

Put the code to load combo2 in the click event of Combo1.

OR...

you could use recordset to to show all fields

Code:

---

set rs=CreateObject("ADODB.Recordset")
rs.cursorlocation=adUseClient
rs.open "SELECT * FROM [" & Combo1.Text & "]", conn

for i=0 to rs.fields.count-1
  Combo2.AddItem rs.fields(i).name
next i

rs.close
set rs=nothing

---