Open schema column

[david maddison]

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

[fungi]

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

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

[david maddison]

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

[brucevde]

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.

[andreys]

OR...

you could use recordset to to show all fields

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