Looking up tables & fields in MS Access 2000

[ktrang]

I'm trying to create a vb app that allows a user to specify an Access dbase at runtime and to select the desired fields (and tables) to add to a report.

How do you look up the available tables and columns in an Access dbase with code or sql?

 

[ufobaby]

here goes your code ->
You will need a form and
-> Combobox - cmbTables
-> ListView - lstField
-> Text Box - txtdesc
'==== First for getting Table Names
Public Sub FillTable()
'==== Fill the Combo with all the Tables ====
Dim TDef As Long
Do While dbEditor.TableDefs.Count <> TDef
If dbEditor.TableDefs(TDef).Attributes = 0 Then
'Attributes 0 are not system tables, so we only take those ones
cmbTables.AddItem dbEditor.TableDefs(TDef).Name
End If
TDef = TDef + 1
Loop
End Sub
'==== Secondly for getting the Filed Names
Private Sub cmbTables_Click()

Dim rsField As Recordset
Dim ctrFld As Long
Dim lt As ListItem
Dim Desc As String

If cmbTables.Text <> &quot;&quot; Then

lstField.GridLines = True
lstField.ListItems.Clear
lstField.ColumnHeaders.Add , , &quot;FIELDS&quot;
lstField.ColumnHeaders.Add , , &quot;DESCRIPTION&quot;
lstField.HotTracking = True

Set rsField = dbEditor.OpenRecordset(cmbTables, dbOpenTable)
Do While rsField.Fields.Count <> ctrFld
Set lt = lstField.ListItems.Add(, , rsField.Fields(ctrFld).Name)
Desc = rsField.Fields(ctrFld).Properties(&quot;Description&quot
lt.SubItems(1) = Desc
ctrFld = ctrFld + 1
Loop
txtDesc.Text = dbEditor.TableDefs(cmbTables).Properties(&quot;Description&quot
End If
=======================================================
If u have any doubts just write in ......

Regards
Niraj

 

[ktrang]

Thanks for the reply. Couple of questions though. It looks like the code fills in the combo box and listview with the table & column names. But not sure if or how the code retrieves the table names and column names from the database. Also not sure what the dbEditor is. It looks like this object is used to store/access the dbase info.

Thanks, Kiet

 

[Foada]

Give this a try. You will need to reference MS ActiveX Data objects and MS ADO Ext 2.x for DLL And Security.

Add a command button named cmdTables
Add a combobox named cmbTables
Add a combobox named cmbFields and set its index to 0

Module variable

Private hDB As New ADODB.Connection
Private sPath As String

Module Constant

Private Const sDBConnection As String = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info = False;&quot; & _
&quot;Data Source = &quot;

Add the following code

Private Sub Form_Load()

sPath = &quot;C:\myDatabase.mdb&quot;
hDB.ConnectionString = sDBConnection & sPath
hDB.Mode = adModeReadWrite
hDB.Open

End Sub

Private Sub cmdTables_Click()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim iX As Long

Set cat.ActiveConnection = hDB

For Each tbl In cat.Tables
cmbTables.AddItem tbl.Name
For Each col In tbl.Columns
cmbFields(iX).AddItem col.Name
Next
iX = iX + 1
Load cmbFields(iX)
cmbFields(iX).Left = cmbFields(iX - 1).Left
cmbFields(iX).Top = cmbFields(iX - 1).Top + 490
cmbFields(iX).Visible = True
Next

Set cat = Nothing
Set col = Nothing
Set tbl = Nothing

End Sub

The code will add each table's name to the cmbTables combobox and will add a cmbFields combobox with the field name for each of the tables. You will need to adjust the size of your form for the number of tables you have as if you have a lot of tables there will be a lot of combo boxes. Hope this helps. Anything is possible, the problem is I only have one lifetime.

 

[ufobaby]

hi, apologies for asuming things,

dbEditor is a database Object
So you will need to take &quot;Microsoft DAO 2.5/3.1&quot; refrence in your projec first. Then ->

Dim dbEditor as Database
Set dbEditor = OpenDatabase(&quot;Path and Database Name&quot

For Filling in the Table Names we are looping through the TableDef Collections and getting the tables made by u and not what MsAccess makes (MSYS... etc)

rsEditor is the Recordset object used to retrive the fields info from a table, also to write SQL stmt.
For The Column Name,as u select the table name we loop thru the fields and get the names using ->
Set lt = lstField.ListItems.Add(, , rsField.Fields(ctrFld).Name)

If u are using DAO then you can use the Code i gave, else if u using ADO then foada's code would be required. But usng ADO u will not able to get the Description of the fields ie.
Desc = rsField.Fields(ctrFld).Properties(&quot;Description&quot
lt.SubItems(1) = Desc

Hope this helps u, else donot hesitate to ask
Regards
Niraj

 

[ktrang]

Thanks for the help! It worked.