Listing tables in an Access database 1

[Adamba]

Hiya All

Is there anyway to list all the tables that exsist in an Access 97 database?

E.g.

Database: Weeks
Tables : 26/03/02
25/04/01
...

and append each of these tables into a list box on a Form.

Thanks in advance

Adam

 

[sunaj]

Hi,

Here is a little project that demonstrates that. You can change the comboboxes to list boxes if you wish...

------------------------------------------------------------
'This code demonstrates how to get the table
'and field names from an Access database using ADO2.5
'It also shows how to use comboboxes to view the table and field names.

'Make a reference to
'Microsoft ActiveX Data Objects 2.5 Library (msado15.dll) and
'Microsoft ADO Ext. 2.5 for dll and security (MSADOX.DLL)

'Create a form with 2 comboboxes (combo1 and combo2)
'Change the name of the database in the StrCon to
'the name of your database. Copy this code into the form.

Option Explicit
Dim con As ADODB.Connection
Dim Cat As ADOX.Catalog


Private Sub Form_Load()
Dim StrCon As String
Dim i As Integer

'Connection string, remember to change the name of the databse.
StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\tmp\test.mdb"
'Open connection
Set con = New ADODB.Connection
con.Open StrCon
'Set the catalogs connection
Set Cat = New ADOX.Catalog
Cat.ActiveConnection = StrCon
'Loop through the tables and add them to combo1
For i = 0 To Cat.Tables.Count - 1
If Left(Cat.Tables(i).Name, 4) <> &quot;MSys&quot; Then _
Combo1.AddItem Cat.Tables(i).Name
Next i
Combo1 = Combo1.List(0)
combo1_click
End Sub

Private Sub combo1_click()
Dim i As Integer
'When user selects a new table name in combo1,
'show the field names in combo2.
Combo2.Clear
For i = 0 To Cat.Tables(Combo1.Text).Columns.Count - 1
Combo2.AddItem Cat.Tables(Combo1.Text).Columns(i)
Next i
Combo2 = Combo2.List(0)
End Sub

Private Sub Form_Unload(Cancel As Integer)
'close connection
con.Close
End Sub
------------------------------------------------------------ Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Adamba]

Sunaj,

Thanks for the help, it works exactly as i wanted!!
Youve saved my brain from its usual pain!

Thanks again.

Adam