Create a list from Table coulmn headings
- Thread starter crosmill
- Start date
- Local time
- Today, 15:35
- Joined
- Feb 19, 2002
- Messages
- 47,594
The combobox has an option that shows the field list of a table or query. The wizard will not build this combo for you, you'll have to build it yourself. Choose FieldList as the RowSourceType.
crosmill
Registered User.
- Local time
- Today, 20:35
- Joined
- Sep 20, 2001
- Messages
- 285
Thanks Pat I came across that in the help files.
Unfortunatly, I need to create the list on an ASP page, so unless I pull the names out from a SQL statement, or create the list in a query it's no good.
information.schema doesn't work either.
cheers
Unfortunatly, I need to create the list on an ASP page, so unless I pull the names out from a SQL statement, or create the list in a query it's no good.
information.schema doesn't work either.
cheers
- Local time
- Today, 14:35
- Joined
- Feb 28, 2001
- Messages
- 30,501
The only way to get Access metadata into a page is to put it into a table first so it can be queried. Well, that's not exactly true, but as a long-time security manager, I'll tell you ahead of time not to ask about the other ways to do this. I refuse to divulge any method that would expose you to greater security risks. That WON'T be on my conscience.
The way to make the metadata table involves running some VBA code to step through the Tabledefs("table-name").Fields(number) collection where the number steps from 1 to Tabledefs("table-name").Fields.Count, which tells you how many fields you have in the given table. If you needed this for ALL tables, you could iterate over Tabledefs(another-number) where THAT number steps from 1 to Tabledefs.Count, which tells you how many tables you have. But you should avoid table names that have the SYSTEM attribute set. Whether you also avoid names that have the HIDDEN attribute is your call.
This complicated methodology is necessary because you are asking Access to let you see things it normally considers as part of its own, private metadata. (Literally in this case, "the data beyond or outside of the data.")
You would have to write something to drive this operation at any time after one or more of your table structures changed. But you could do it with a recordset and a simple VBA loop, perhaps plus a stored query that erases your metadata table before you run the loop. If the erasure is a stored action query, you could run a DoCmd.OpenQuery on the erase query, then close that query and go about the rest of your business.
Now, the down side. You have to run the VBA code after EVERY CHANGE TO ANY INCLUDED TABLE! You will have to determine how to decide that for yourself. (Or you could just run it yourself once per day or once per week or however often you wish to reconcile the stuff you are allowing folks to see with the stuff stored in the metadata table.)
The way to make the metadata table involves running some VBA code to step through the Tabledefs("table-name").Fields(number) collection where the number steps from 1 to Tabledefs("table-name").Fields.Count, which tells you how many fields you have in the given table. If you needed this for ALL tables, you could iterate over Tabledefs(another-number) where THAT number steps from 1 to Tabledefs.Count, which tells you how many tables you have. But you should avoid table names that have the SYSTEM attribute set. Whether you also avoid names that have the HIDDEN attribute is your call.
This complicated methodology is necessary because you are asking Access to let you see things it normally considers as part of its own, private metadata. (Literally in this case, "the data beyond or outside of the data.")
You would have to write something to drive this operation at any time after one or more of your table structures changed. But you could do it with a recordset and a simple VBA loop, perhaps plus a stored query that erases your metadata table before you run the loop. If the erasure is a stored action query, you could run a DoCmd.OpenQuery on the erase query, then close that query and go about the rest of your business.
Now, the down side. You have to run the VBA code after EVERY CHANGE TO ANY INCLUDED TABLE! You will have to determine how to decide that for yourself. (Or you could just run it yourself once per day or once per week or however often you wish to reconcile the stuff you are allowing folks to see with the stuff stored in the metadata table.)
Similar threads
