create query dynamically

[Guest]

hi,

I have the following behind a button:

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

Set cat.ActiveConnection = CurrentProject.Connection


sqltext = "SELECT " _
&
"tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
" _
& "tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
" _
&
"Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),'#,###.#') " _
& "AS [SumOfCountOfLoan Acct #], 'line2' AS line" _
& "from tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY " _
& "WHERE
(((tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus) = " _
& "[Forms]![frm_criteria]![tester1])) " _
& "GROUP BY
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
" _
& "tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt;
" _
& "UNION ALL " _
& "SELECT ResolvedStatus, [1calltotals2Rjuly130_finaltemp].ResolvedDt, " _
& "[1calltotals2Rjuly130_finaltemp].Expr1, 'line5' AS line " _
& "FROM 1calltotals2Rjuly130_finaltemp " _
& "GROUP BY ResolvedStatus, [1calltotals2Rjuly130_finaltemp].ResolvedDt,
[1calltotals2Rjuly130_finaltemp].Expr1; "

'Set QueryDef = Database.CreateQueryDef(try_this, sqlTest)
' be sure to add Microsoft ADO Ext. 2.1 for DDL and Security library

MsgBox "see"

cmd.CommandText sqltext
cat.Views.Append "My_Query", cmd

I am trying to create a saved query dynamically, or even a temp query for
that matter. This will be used as a graph source. I am having no luck, and
before this, I tried the createquerydef without any luck.

Can someone please help?

Thanks in advance,
geebee

 

[pietlinden]

couple of questions:
1. If you create your SQL statement, and print it to the Immediate
window, does it run if you copy and paste it into the SQL property of a
query grid?
2. Why are you using what looks like a cross between DAO and ADO?
That's really confusing.

Why not just do something like

Function GenerateSQL(arg1, arg2...) AS String
'--- build SQL statement here
GenerateSQL="...."

End function

and then

currentdb.Querydefs("MyQuery").SQL=GenerateSQL("arg1","arg2"..)