Save SQL to An Existing Query?

[Guest]

Hi

Can i save an SQL statement to an existing Query. what i would like to do is
have a crosstab on fields that will be determined at runtime. so i would have
the pivot on a field that is chosen at runtime. I can run a sub that will
generate the correct SQL, i am just lost on how to save it to an existing
query.

the full blown explanation of what i am try to do, is the following. i have
a report which is going to use a cross tab. so i need to attach a query to
the report. if i assign report.recordsource = SQL i get a message that a
crosstab can't be attached to the report. but if i use a saved crosstab and i
write report.recordsource = me.myCrosstabQry then it works. don't know why,
but it does.

thanks in advance for your help,

sam

 

[Guest]

Dim strSQL As String
Dim dbcurr As Database
Dim qdfCurr




strSQL = 'Put your SQL string here
Set dbcurr = CurrentDb
Set qdfCurr = dbcurr.QueryDefs("SQLSummary")
qdfCurr.SQL = strSQL

 

[Guest]

Sorry, did not full clarify
Dim strSQL As String
Dim dbcurr As Database
Dim qdfCurr

strSQL = 'Put your SQL string here
Set dbcurr = CurrentDb
Set qdfCurr = dbcurr.QueryDefs("YourQueryName")
qdfCurr.SQL = strSQL

 

[Guest]

how do i do this using ADO

Sorry, did not full clarify
Dim strSQL As String
Dim dbcurr As Database
Dim qdfCurr

strSQL = 'Put your SQL string here
Set dbcurr = CurrentDb
Set qdfCurr = dbcurr.QueryDefs("YourQueryName")
qdfCurr.SQL = strSQL

 

[Guest]

This is from the help menu on how to create a query using ADO. I have not
tried it, but it should get you on the right path.

Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command

Set cat.ActiveConnection = CurrentProject.Connection
cmd.CommandText “Select * From Employeesâ€
Cat.Views.Append "NewQuery", cmd

 

[Guest]

Thanx a lot. i was reading about this. and i wasn't sure, i have never used
ADOX. i tried it, but for ADOX a new reference is required. i am always
afraid of those references because of version upgrades. my job is hard enough
designing complicated forms and queries, i don't have enough time read up on
writing code which validates versions.

again, thanks a lot, i will try to implement the code.

sam