Save a query as an SQL Statement into a Table

[Bob1]

I have a couple of different ways of producing mailing lists from
forms in my database. Users don't have access to the database window.
Once the list is created users can save the list by typing a new name
for the list into a text box and the end result is saved as a table
with this name with a Make Table Query. My thinking is that it would
be better for users to be able to save these lists as SQL Statements.
These could be stored into a table as presumably this would use up
less less space and would save having a lot of tables with duplicate
data. Can someone help me with a way of doing this?
Thanks Bob

 

[Daryl S]

Bob1 -

You need to decide what you are saving the query or results for. If you
save the data (as you are now with tables), then you will always be able to
see the mailing list that was used at the time the table was created. Is
this information historically significant in that you must keep track of the
results at that time? Will the user be re-using this list, or would they
want to create a new list using the same criteria? If the business need is
to save the criteria rather than the data, then you can save the query
instead of the results. Look up the CreateQueryDef method to do this. If
there is a need for users to only see their own queries, then when you save
them, prepend the name with the user's logon name, and then you can display a
list of just their saved queries for them to choose from.

 

[Bob1]

Bob1 -

You need to decide what you are saving the query or results for.  If you
save the data (as you are now with tables), then you will always be able to
see the mailing list that was used at the time the table was created.  Is
this information historically significant in that you must keep track of the
results at that time?  Will the user be re-using this list, or would they
want to create a new list using the same criteria?  If the business need is
to save the criteria rather than the data, then you can save the query
instead of the results.  Look up the CreateQueryDef method to do this.  If
there is a need for users to only see their own queries, then when you save
them, prepend the name with the user's logon name, and then you can display a
list of just their saved queries for them to choose from.

Daryl S Thats really helpful. I hadn't come across CreateQueryDef
before. The important thing is to save the criteria rather than the
history so this will work fine
Thanks very much.
Bob