Populate a list box with names of tables in current database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a routine to return a list of all tables in the current database
I have experimented with Containers, Collections, TableDefs
After wasting 2 days, I would appreciate any help
 
David said:
I need a routine to return a list of all tables in the current database.
I have experimented with Containers, Collections, TableDefs.
After wasting 2 days, I would appreciate any help.
Click to expand...

Hi David

I use the following code to populate a combo box with the table names in the
current database (excluding system tables):

'*** code start ***
Private Sub Form_Open(Cancel As Integer)
Dim strRowSource As String
Dim TblDef As TableDef

Set db = CurrentDb
For Each TblDef In db.TableDefs
If InStr(TblDef.name, "Sys") = 0 Then
strRowSource = strRowSource & "'" & TblDef.name & "';"
End If
Next
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
cboTableDefs.RowSource = strRowSource

Set db = Nothing
End Sub
'*** code end ***

and when I select a table name from the combo box, the following code puts
all the field names from the selected table into a listbox:

'*** code start ***
Private Sub cboTableDefs_AfterUpdate()
Dim Feeld As Field
Dim strRowSource As String

If IsNull(cboTableDefs) Then Exit Sub

Set TblDef = db.TableDefs(cboTableDefs)
For Each Feeld In TblDef.Fields
strRowSource = strRowSource & "'" & Feeld.name & "';"
Next
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
lstFields.RowSource = strRowSource
lstFields.Requery
End Sub
'*** code end ***

Regards - Joe
 
A Query with SQL String:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (MSysObjects.Name Not Like "MSys*")
AND (MSysObjects.Type=1 Or MSysObjects.Type=6);

should give you a list of (non-system) Tables. 1 is for local Tables and 6
is for linked Tables.
 
Back
Top