Database Table/Field Information

[Rose B]

Hi,

I need to access details (names) about the tables and fields that are in my
database so that they can be selected via a combo box..... I AM SURE that
this is possible as I think I have come across it before but now cannot find
anything on it. Can anyone help?

 

[Dale Fye]

Rose,

To get the names of the tables, you can either loop through the TableDefs
collection

Dim tdf as dao.tabledef 'requires a reference to DAO
For each tdf in currentdb.tabledefs
if instr(tdf.name, 'mSys') = 0 then debug.print tdf.name
Next tdf

Or use the mSysObject system table. Something like:

SELECT msysobjects.Name AS TableName, msysobjects.Type, *
FROM msysobjects
WHERE msysobjects.Type=1

If you have linked tables, the Type values are different for those (don't
recall off the top of my head)

For the field names, the easiest way to do it is to set the combo box's
RowSource to the name of the table, then set the Row Source Type to Field
list.

The other way would be to open a recordset based on the table, and loop
through the Fields.

HTH
Dale

 

[Allen Browne]

Set the combo's Row Source Type property to:
Field List
Then set its Row Source to the name of your table.
The combo will show the fields from the table.

If you do want to list the fields programmatically, here's an example of
how:
http://allenbrowne.com/func-06.html