Displaying Table Names on a form

Another way ...supposing you have a combobox name cboMyTables and a button named cmdPopulateCombo

Private Sub cmdPopulateCombo_Click()
Dim tdf As TableDef
Me.cboMyTables.RowSourceType = "Value List"
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 5) = "Work_" Then Me.cboMyTables.AddItem (tdf.Name)
Next
End Sub

Select allOpen in new window

Thanks Ryan!  That does show me all of my tables but how do I make it display the data?

Just to be clear, I would never do this in an application that other people will have access to.  Application users should never have direct access to your tables.  Having said that, if you really want to do this, your best bet would be to:

1.  create and save (qry_TableData) a query, with the syntax:

SELECT * FROM Work_Employees

Select allOpen in new window

2.  Add a subform control to your form, name it sub_TableData, but don't fill in the sourceobject

3.  in the AfterUpdate event of your combo box (assumes the combo is named cbo_Tables), use this code:

Private Sub cboTables_AfterUpdate

    currentdb.querydefs("qry_TableData").SQL = "SELECT * FROM [" & me.cbo_Tables & "]"
    me.sub_TableData.SourceObject = "qry_TableData"

End Sub

Select allOpen in new window

Thanks for the help.  

I completely understand why you wouldn't give access to the tables but one person has to be able to manipulate the data.

A couple of things on the comment:

1.  When I create the query and copy the code under the AfterUpdate, I get the error, a problem occured while microsoft access was communicating with the ole server or activex control.  The qry is named qry_TableData

Private Sub cmb_Tables_AfterUpdate()

CurrentDb.QueryDefs("qry_TableData").SQL = "SELECT * FROM [" & Me.cmb_Tables & "]"
Me.sub_TableData.SourceObject = "qry_TableData"

End Sub

Select allOpen in new window

2.  Am I able to do this without creating a qry?  I am going to have 30+ tables and I don't want to have to create a query for each one of them (if possible).

My code works fine by the way...

@Ryan,

You are only going to create one query (qry_TableData), and are going to update that query each time you change the table selection.  Try changing the code in the after update event to:

Private Sub cmb_Tables_AfterUpdate()

    docmd.echo false
    me.sub_TableData.SourceObject = Null
    CurrentDb.QueryDefs("qry_TableData").SQL = "SELECT * FROM [" & Me.cmb_Tables & "]"
    Me.sub_TableData.SourceObject = "query.qry_TableData"
    docmd.echo true

End Sub

Select allOpen in new window

Perfect!  Thanks Dale!

glad I could help.

Actually, you don't need the query afterall. you can simply use:

me.sub_TableData.Sourceobject = "Table." & me.cmb_Tables

Select allOpen in new window

That should work as long as your table names don't contain any spaces.