List Table and Field names of database

[julie]

I would like to be able list the table names and their fields in two seperate
combo boxes using an ADO connection. What would be the best way to accomplish
this? Upon loading the application I am listing several possible DB's in
listbox. The connection is then made from the selected DB. I would then
like to populate the above mentioned combo boxes with the appropriate items.
Hopes this makes sense.
TIA
jill

[Arthur Wood]

Julie,
What kind of database are you accessing? Access, SQL Server ???

Arthur Wood

"julie" <meils@bostwick.com> wrote:
>
>I would like to be able list the table names and their fields in two seperate
>combo boxes using an ADO connection. What would be the best way to accomplish
>this? Upon loading the application I am listing several possible DB's in
>listbox. The connection is then made from the selected DB. I would then
>like to populate the above mentioned combo boxes with the appropriate items.
> Hopes this makes sense.
>TIA
>jill

[Carl Ray]

First, to list the table names, use the OpenSchema method:

. . .
cn.Open
. . .
Set rs = cn.OpenSchema(adSchemaTables)
. . .
'Populate Combo Box
Do Until rs.EOF

'Make sure we're using the names of Tables
'that are not System Object Tables (start with MSys).
If rs.Fields("TABLE_TYPE") = "TABLE" Then
'Populate the Combo Box
Combo1.AddItem rs.Fields("TABLE_NAME")
End If

rs.MoveNext

Loop
. . .
rs.Close



Next, any time you click on a table name in List1:

Dim i As Integer

. . .
rs.Open "SELECT * FROM [" & Combo1.Text & "]", cn
. . .
Combo2.Clear

For i = 0 To rs.Fields.Count - 1

Combo2.AddItem rs.Fields(i).Name

Next i

rs.Close


And that should populate your second combo box with the names of the fields
in the selected table.


Carl
http://www.vbnexus.com


"julie" <meils@bostwick.com> wrote:
>
>I would like to be able list the table names and their fields in two seperate
>combo boxes using an ADO connection. What would be the best way to accomplish
>this? Upon loading the application I am listing several possible DB's in
>listbox. The connection is then made from the selected DB. I would then
>like to populate the above mentioned combo boxes with the appropriate items.
> Hopes this makes sense.
>TIA
>jill

[Carl Ray]

I typed the comment "Next, any time you click on a table name in List1:" about
halfway through the code sample, when what I meant to say was "Next, any
time you click on a table name in COMBO1:".

Sorry for the error.

Carl
http://www.vbnexus.com

"Carl Ray" <carlray@vbnexus.com> wrote:
>
>First, to list the table names, use the OpenSchema method:
>
> . . .
> cn.Open
> . . .
> Set rs = cn.OpenSchema(adSchemaTables)
> . . .
> 'Populate Combo Box
> Do Until rs.EOF
>
> 'Make sure we're using the names of Tables
> 'that are not System Object Tables (start with MSys).
> If rs.Fields("TABLE_TYPE") = "TABLE" Then
> 'Populate the Combo Box
> Combo1.AddItem rs.Fields("TABLE_NAME")
> End If
>
> rs.MoveNext
>
> Loop
> . . .
> rs.Close
>
>
>
>Next, any time you click on a table name in List1:
>
> Dim i As Integer
>
> . . .
> rs.Open "SELECT * FROM [" & Combo1.Text & "]", cn
> . . .
> Combo2.Clear
>
> For i = 0 To rs.Fields.Count - 1
>
> Combo2.AddItem rs.Fields(i).Name
>
> Next i
>
> rs.Close
>
>
>And that should populate your second combo box with the names of the fields
>in the selected table.
>
>
>Carl
>http://www.vbnexus.com
>
>
>"julie" <meils@bostwick.com> wrote:
>>
>>I would like to be able list the table names and their fields in two seperate
>>combo boxes using an ADO connection. What would be the best way to accomplish
>>this? Upon loading the application I am listing several possible DB's
in
>>listbox. The connection is then made from the selected DB. I would then
>>like to populate the above mentioned combo boxes with the appropriate items.
>> Hopes this makes sense.
>>TIA
>>jill
>

[julie]

Thanks Carl. BTW. I like you new web set. The tutorials have been very helpful
to a beginner programmer like myself. I look forward to many more to come.
julie

"Carl Ray" <carlray@vbnexus.com> wrote:
>
>I typed the comment "Next, any time you click on a table name in List1:"
about
>halfway through the code sample, when what I meant to say was "Next, any
>time you click on a table name in COMBO1:".
>
>Sorry for the error.
>
>Carl
>http://www.vbnexus.com
>
>"Carl Ray" <carlray@vbnexus.com> wrote:
>>
>>First, to list the table names, use the OpenSchema method:
>>
>> . . .
>> cn.Open
>> . . .
>> Set rs = cn.OpenSchema(adSchemaTables)
>> . . .
>> 'Populate Combo Box
>> Do Until rs.EOF
>>
>> 'Make sure we're using the names of Tables
>> 'that are not System Object Tables (start with MSys).
>> If rs.Fields("TABLE_TYPE") = "TABLE" Then
>> 'Populate the Combo Box
>> Combo1.AddItem rs.Fields("TABLE_NAME")
>> End If
>>
>> rs.MoveNext
>>
>> Loop
>> . . .
>> rs.Close
>>
>>
>>
>>Next, any time you click on a table name in List1:
>>
>> Dim i As Integer
>>
>> . . .
>> rs.Open "SELECT * FROM [" & Combo1.Text & "]", cn
>> . . .
>> Combo2.Clear
>>
>> For i = 0 To rs.Fields.Count - 1
>>
>> Combo2.AddItem rs.Fields(i).Name
>>
>> Next i
>>
>> rs.Close
>>
>>
>>And that should populate your second combo box with the names of the fields
>>in the selected table.
>>
>>
>>Carl
>>http://www.vbnexus.com
>>
>>
>>"julie" <meils@bostwick.com> wrote:
>>>
>>>I would like to be able list the table names and their fields in two seperate
>>>combo boxes using an ADO connection. What would be the best way to accomplish
>>>this? Upon loading the application I am listing several possible DB's
>in
>>>listbox. The connection is then made from the selected DB. I would then
>>>like to populate the above mentioned combo boxes with the appropriate
items.
>>> Hopes this makes sense.
>>>TIA
>>>jill
>>
>