Run dynamic SQL and show it as a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a report that on open it runs a sub so that it displays a cross tab
query. i would like to show this data as if its a stored query. please let me
explain.

in order to have this report show my results, the module must run so that
SQL is produced. then it is attached to the report record source. what i am
trying to do now is have it displayed like a query.

i am thinking of docmd.runquery but this requires a stored query. run SQL
requires an action query. one other option is to run the SQL and store it a
temp table, but this will not work for me since i don't know how many columns
i will have until run time.

please adise,

thanks,

sam
 
SAm said:
I have a report that on open it runs a sub so that it displays a cross tab
query. i would like to show this data as if its a stored query. please let me
explain.

in order to have this report show my results, the module must run so that
SQL is produced. then it is attached to the report record source. what i am
trying to do now is have it displayed like a query.

i am thinking of docmd.runquery but this requires a stored query. run SQL
requires an action query. one other option is to run the SQL and store it a
temp table, but this will not work for me since i don't know how many columns
i will have until run time.
Click to expand...


If you have a saved query, the you can just assign it a new
SQL statemenr using code similar to:
CurrentDb.QueryDefs!nameofquery = "sql statement"
 
additionally-- if you are usnig MS Access in the reccomended way, and thus
doing Access Data Projects then you can also do this



Public Sub TestTempView()

Dim strSql As String
strSql = "Create procedure #AaronIsHappy AS Select * from Sysobjects"

DoCmd.RunSQL strSql

Dim rst As New ADODB.Recordset
rst.Open "EXEC #AaronIsHappy", CurrentProject.Connection

End Sub
 
Back
Top