How to get name of table from mdb file

[Vincent81]

HI

I have a problem with VB, maybe you all can help me.
i have 2 combobox which i need add the name of tables and fields that have been creared in Ms Access file
And i use ADODB connection to access my DATABASE
thanks you

 

[alfredp77]

Use the "OpenSchema" method of your connection object:

Dim mConn As New ADODB.Connection
Dim rstTables As ADODB.Recordset

'Assuming that u've already open the connection to
'the Access database using mConn:
Set rstTables = mConn.OpenSchema(adSchemaTables)

With rstTables
Do While Not .EOF
'add the table names to cmbTable combobox
cmbTable.AddItem .Fields("TABLE_NAME&quot.Value
.MoveNext
Loop
End With

To get the field names, use "adSchemaColumns".
Check the MSDN help on OpenSchema method for more detailed information.

 

[sunaj]

-------------------------------------------------
'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'

 

[PraveenMenon]

Let me explain one way via DAO...

'===========================================================
Dim DBName As Database

Dim tblObj As DAO.TableDef
Dim gnReadOnly As Integer
Dim sConnect As String
Dim sDBName As String
sDBName =app.path & &quot;/data/db.mdb&quot;
Set DBName = OpenDatabase(sDBName, False, gnReadOnly, sConnect)
For Each tblObj In DBName.TableDefs
If (tblObj.Attributes And dbSystemObject) = 0 Then
MsgBox tblObj.Name
End If
Next
'=========================================================== All the Best
Praveen Menon
pcmin@rediffmail.com