Adding tables to DB on "the fly"

I want the program user to be able to add tables to a database as needed. It appears that Database.FieldSchema will allow this to occur. What I’d like to do then is be able to list all the tables under a specific database that would show both those tables that were built when the program opened its first time and the tables added by the program user. Fields do not need to be listed. I would like a list box to show all the table names. I have not been able to come up with a way to show the tables which I believe is important as the user must know what he has for tables to work with.

I’d appreciate any comments, thoughts and/or solutions you might have. Thank you.

Database.TableSchema returns a RecordSet containing the tables in the database. Database.FieldSchema returns a RecordSet containing the columns of a specific table. Neither are used to modify the DB.

To modify the DB you would use SQL (CREATE TABLE, ALTER TABLE, etc.) for the database you are using.

But I have to say, allowing end-users to add tables to the database does not seem like a good idea to me.

Thank you for the response Paul.

My problem is that the number recordsets that could occur under a given table might be several hundred or more. Sorting through these might be difficult and time consuming so my idea was to build new tables, identical to the original, so that recordsets pertaining to product D could all be put togther, product C together and so forth.

If you have the time, could you give me an idea of how you would save large numbers of recordset on a table and still make them easily sortable? A search would work, but what if the person doesn’t know the exact name to use for the search. Then he has to scroll which I’m trying to avoid.

Again, thank you.

I don’t understand what you mean by “saving record sets”. In general, the number of rows is irrelevant, you just SELECT what you want and work with the resulting data.

Perhaps you could describe your problem at a higher level as I don’t really understand what your issue is.

I think I have solved my problem having talked with you. By adding one more field I can sort through the records in the table and list only the pertinent records needed. This will work nicely for me. Some times it seems the obvious answers are to near to be seen. Again, thank you.


You might also want to look into Temporary Tables. I know MySQL lets you clone an existing table schema into a temporary table. I do this quite often.