Saving SQL String

[icthus123]

I'm creating a database and the end user would like to be able to save queries which have been created dynamically with a date next to them. Now if i save the actual query that would surely take up far too much database space. So i'm wondering if i can just save the SQL String for the query in a table with a date.

Is it possible to access the SQL of a given query with VBA or will i have to store the SQL string somewhere before i use it to create the query and then retrieve it if the user wishes to save it?

Any ideas? Thanks!

[XLGibbs]

Yes, but if the query is built dynamically in code, it already exists in a string (presumably).

You can just pass that string to a text field which (hopefully) would have enough room to store the SQL.

If the parameters of date are the only thing that are dynamic, you might be able to create a table to store the date the query was executed and which parameters were passed. Essentially update that table at the same time the query is run.

[icthus123]

Yes, but if the query is built dynamically in code, it already exists in a string (presumably).

You can just pass that string to a text field which (hopefully) would have enough room to store the SQL.

If the parameters of date are the only thing that are dynamic, you might be able to create a table to store the date the query was executed and which parameters were passed. Essentially update that table at the same time the query is run.

Right, that's what i will probably do. but i can't help but think that if i could strip the SQL out of a query it would be easier, if it's possible to do that? because sometimes the user won't want to save it. or am i just making things over complicated?

[Norie]

I don't think you are making things over-complicated.

I think it's the user that is.

What is the point in saving these dynamically created queries?

[XLGibbs]

The querydef would be stored only if the query is actually saved in the database. If it were built and not saved as a query (and thus visible in the query window)..it would not likely be retrievable.

[icthus123]

I don't think you are making things over-complicated.

I think it's the user that is.

What is the point in saving these dynamically created queries?

Well, the reason is that they want to use the reports built with them to send out as invoices. So they want to have a record of what they've sent out. Is there a better way of doing this than saving the Query SQL?


The querydef would be stored only if the query is actually saved in the database. If it were built and not saved as a query (and thus visible in the query window)..it would not likely be retrievable.

Right, well I will be storing the query because i'm using it to create a report. So does that mean there's a way of retrieving it?

[XLGibbs]

Yes, an if parameters are passed through it report run time the form you would set up to identify those would involce code, and that code could be written to store the parameters. You don't necessarily need to store the whole query..just the parameters.