Retrieve table names and field names

[John J.]

In a mailing form I would like the user to be able to select one of
database's tables and after that he should be able to select one or more
fields of that table. In VBA how can I retrieve all database 's tablenames
and how can I retrieve the fieldnames of one the tables.

Thank you,
John

 

[Douglas J. Steele]

Using combo boxes would be your best bet. Let's assume you name them
cboTableList and cboFieldList.

To load cboTableList with the names of the tables, set its RowSourceType to
Table/Query, and the RowSource to:

SELECT Name FROM MSysObjects WHERE Type IN (1, 4, 6) And Name NOT LIKE
'MSys*' ORDER BY NAME

For the AfterUpdate event, put code like:

Private Sub cboTableList_AfterUpdate()

Me.cboFieldList.RowSourceType = "Field List"
Me.cboFieldList.RowSource = Me.cboTableList

End Sub

 

[John J.]

Thanks! Works great.

John

Using combo boxes would be your best bet. Let's assume you name them
cboTableList and cboFieldList.

To load cboTableList with the names of the tables, set its RowSourceType
to Table/Query, and the RowSource to:

SELECT Name FROM MSysObjects WHERE Type IN (1, 4, 6) And Name NOT LIKE
'MSys*' ORDER BY NAME

For the AfterUpdate event, put code like:

Private Sub cboTableList_AfterUpdate()

Me.cboFieldList.RowSourceType = "Field List"
Me.cboFieldList.RowSource = Me.cboTableList

End Sub