access tables

[len]

How do I list a group of tables that I would find in a access database in a listbox. I'm constructing a genelogy tree and I want to be able to pick a table representing a family from a number of tables.

[Astinite]

I think if you run the "sp_tables" command on any database it will return a recordset that includes all the table names.

[Alec Ruderman]

Try using the OpenSchema function of the ADO Connection object. It allows you, as long as your datasource supports it, to retrieve various information about the schema of the database you connected to. In particular use

adSchemaTables

to return the recordset you need. An example right out of MSDN is below.


public Sub OpenSchemaX()

Dim cnn1 as ADODB.Connection
Dim rstSchema as ADODB.Recordset
Dim strCnn as string

set cnn1 = new ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn

set rstSchema = cnn1.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
Debug.print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close

cnn1.Close

End Sub