save an query by sql code and deleting the saved query

[Richard]

hi there,

I have a TRANSFORM query

TRANSFORM Sum([dbo_SalesQuery].[aantbest]) AS SumOfaantbest
SELECT [dbo_SalesQuery].[cddeb], Sum([dbo_SalesQuery].[aantbest]) AS Totaal
FROM dbo_SalesQuery
WHERE (([dbo_SalesQuery].[Artikel] Like "%124507%" Or
[dbo_SalesQuery].[Artikel] Like "%125900%") And
([dbo_SalesQuery].[verkoopdatum]>=#1/1/2007# And
[dbo_SalesQuery].[verkoopdatum]<=#12/31/9999#) And ([dbo_SalesQuery].[cddeb]
Like "%%") And ([dbo_SalesQuery].[vert] Like "%%"))
GROUP BY [dbo_SalesQuery].[cddeb]
PIVOT [dbo_SalesQuery].[Artikel];

And saved it to an query in MS Access.

I want to create the query by running an some sql dynamicly

example:

create query (
TRANSFORM Sum([dbo_SalesQuery].[aantbest]) AS SumOfaantbest
SELECT [dbo_SalesQuery].[cddeb], Sum([dbo_SalesQuery].[aantbest]) AS Totaal
FROM dbo_SalesQuery
WHERE (([dbo_SalesQuery].[Artikel] Like "%124507%" Or
[dbo_SalesQuery].[Artikel] Like "%125900%") And
([dbo_SalesQuery].[verkoopdatum]>=#1/1/2007# And
[dbo_SalesQuery].[verkoopdatum]<=#12/31/9999#) And ([dbo_SalesQuery].[cddeb]
Like "%%") And ([dbo_SalesQuery].[vert] Like "%%"))
GROUP BY [dbo_SalesQuery].[cddeb]
PIVOT [dbo_SalesQuery].[Artikel];
) and save as querytransform

then i want to run another piece of Sql code on it and after its done i need
to delete the query

Delete from querytransform

I hope i make any sence. thx in advance

 

[Allen Browne]

You can use DDL to create a view or procedure, e.g.:
Dim strSql As String
strSql = "CREATE VIEW qry1 as SELECT tblInvoice.* from tblInvoice;"
CurrentProject.Connection.Execute strSql

But it might be easier to just set up a query for the purpose, and write its
SQL property when you need to. For example, you could create a query name
(say) MyCrosstab, and then any time:
strSql = "TRANSFORM ...
CurrentDb.QueryDefs("MyCrosstab").SQL = strSql