Database tables and their fields

[RobertLees]

Hi

I'm using an Access database with ADODB.

I don't have any collection that will allow me to get the names of the tables in the database, and their fields.

I don't care if I have to open it a different way, then close it and re-open as I do now. I just want a list of tables and fields in the database.

Thanks
Robert

[Hack]

Put two listboxes on your form (one for table names, and then when a table is selected, the fields will be displayed in the other.)

'NOTE:  A reference to Microsoft ActiveX Data Objects, and Microsoft ADO 'Ext X.X for DDL and Security must be made
'This makes use of two ListBoxes.   lstTables displays all tables in the
'selected database.   lstFieldStats lists the individually selected field
'(from lstTables) statistics
Private ADOCn As ADODB.Connection
Private ConnString As String
Private MyCat As ADOX.Catalog
Private MyTable As ADOX.Table
 
Private Sub ListTables(DBName As String)
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBName & ";Persist Security Info=False"
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
 
lstTables.Clear
 
Set MyCat = New ADOX.Catalog
MyCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=" & DBName & ";"
 
 For Each MyTable In MyCat.Tables
    'exclude VIEWS
    If MyTable.Type <> "VIEW" Then
        'exclude system tables
        If Left$(MyTable.Name, 4) <> "MSys" Then lstTables.AddItem MyTable.Name
    End If
 Next
Set MyCat = Nothing
 
End Sub
 
Private Sub Command1_Click()
ListTables "c:\program files\mydb.mdb"
End Sub
 
Private Sub lstTables_Click()
Dim MyField As Field
Dim rs As ADODB.Recordset
Dim SQL As String
lstFieldStats.Clear
 
Set rs = New ADODB.Recordset
 
SQL = "SELECT * FROM " & lstTables.List(lstTables.ListIndex)
rs.Open SQL, ADOCn, adOpenForwardOnly, adLockOptimistic
      For Each MyField In rs.Fields
          lstFieldStats.AddItem "Name = " & MyField.Name & " Size = " & MyField.DefinedSize
      Next
rs.Close
Set rs = Nothing
End Sub

[dee-u]

Put two listboxes on your form (one for table names, and then when a table is selected, the fields will be displayed in the other.)

'NOTE:  A reference to Microsoft ActiveX Data Objects, and Microsoft ADO 'Ext X.X for DDL and Security must be made
'This makes use of two ListBoxes.   lstTables displays all tables in the
'selected database.   lstFieldStats lists the individually selected field
'(from lstTables) statistics
Private ADOCn As ADODB.Connection
Private ConnString As String
Private MyCat As ADOX.Catalog
Private MyTable As ADOX.Table
 
Private Sub ListTables(DBName As String)
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & DBName & ";Persist Security Info=False"
Set ADOCn = New ADODB.Connection
[B]ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
[/B]
lstTables.Clear
 
Set MyCat = New ADOX.Catalog
MyCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=" & DBName & ";"
 
 For Each MyTable In MyCat.Tables
    'exclude VIEWS
    If MyTable.Type <> "VIEW" Then
        'exclude system tables
        If Left$(MyTable.Name, 4) <> "MSys" Then lstTables.AddItem MyTable.Name
    End If
 Next
Set MyCat = Nothing
 
End Sub
 
Private Sub Command1_Click()
ListTables "c:\program files\mydb.mdb"
End Sub
 
Private Sub lstTables_Click()
Dim MyField As Field
Dim rs As ADODB.Recordset
Dim SQL As String
lstFieldStats.Clear
 
Set rs = New ADODB.Recordset
 
SQL = "SELECT * FROM " & lstTables.List(lstTables.ListIndex)
rs.Open SQL, ADOCn, adOpenForwardOnly, adLockOptimistic
      For Each MyField In rs.Fields
          lstFieldStats.AddItem "Name = " & MyField.Name & " Size = " & MyField.DefinedSize
      Next
rs.Close
Set rs = Nothing
End Sub

Just intrigued, if you have already specified the connectionstring then why are you still using it as a parameter in the Open method?

[randem]

RobertLees,

Take a look in my signature for Multi Database Connection. The project will give you an idea of how to do it.