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.
Printable View
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.
I think if you run the "sp_tables" command on any database it will return a recordset that includes all the table names.
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