SQL property of query reset after sendobject

[Jon Ley]

I am running some DAO code in an Access 2002 database. The database uses
SQLServer 2000 as it's back end data store. I have a passthrough query that
gets a recordset back from a stored procedure, and another normal query that
further filters this recordset. The sequence of events in my code is as
follows:

Set the parameters for the stored proc by editing the .SQL of the first query.
Use SendObject to send the second query to the user via email (RTF format).
Edit the .SQL of the first query again to modify the parameters
Re-execute the SendObject on the second query.

What is happening is that as soon as I execute the first SendObject, the
..SQL of the _second_ query is getting reset to the default for a new blank
query ("SELECT WITH OWNERACCESS OPTION;")

Edited highlights of my code:

Set db = CurrentDB
Set qdf = db.QueryDefs("PassThrough")
qdf.SQL = "exec proc_thingy 1, 2, 3"
Set qdf = Nothing
If DCount("*", "LocalQuery") > 0 Then
DoCmd.SendObject acSendQuery, "LocalQuery", "Rich Text Format", ...
End If
Set qdf = db.QueryDefs("PassThrough")
qdf.SQL = "exec proc_thingy 1, 4, 5"
Set qdf = Nothing
If DCount("*", "LocalQuery") > 0 Then '*** This line fails ***
DoCmd.SendObject acSendQuery, "LocalQuery", "Rich Text Format", ...
End If

Stepping through the code, the first SendObject works, but as soon as this
has gone the .SQL of "LocalQuery" gets reset.

 

[David Lloyd]

John:

I confirmed what you are seeing and I agree it appears to be resetting with
the first SendObject call. I don't have a solution per se, however, I did
find a reasonable workaround. If you create a new query from the LocalQuery
query (i.e. SELECT * FROM LocalQuery) and use this new query in the
SendObject method, the LocalQuery query is no longer reset, at least in my
tests.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am running some DAO code in an Access 2002 database. The database uses
SQLServer 2000 as it's back end data store. I have a passthrough query that
gets a recordset back from a stored procedure, and another normal query that
further filters this recordset. The sequence of events in my code is as
follows:

Set the parameters for the stored proc by editing the .SQL of the first
query.
Use SendObject to send the second query to the user via email (RTF format).
Edit the .SQL of the first query again to modify the parameters
Re-execute the SendObject on the second query.

What is happening is that as soon as I execute the first SendObject, the
..SQL of the _second_ query is getting reset to the default for a new blank
query ("SELECT WITH OWNERACCESS OPTION;")

Edited highlights of my code:

Set db = CurrentDB
Set qdf = db.QueryDefs("PassThrough")
qdf.SQL = "exec proc_thingy 1, 2, 3"
Set qdf = Nothing
If DCount("*", "LocalQuery") > 0 Then
DoCmd.SendObject acSendQuery, "LocalQuery", "Rich Text Format", ...
End If
Set qdf = db.QueryDefs("PassThrough")
qdf.SQL = "exec proc_thingy 1, 4, 5"
Set qdf = Nothing
If DCount("*", "LocalQuery") > 0 Then '*** This line fails ***
DoCmd.SendObject acSendQuery, "LocalQuery", "Rich Text Format", ...
End If

Stepping through the code, the first SendObject works, but as soon as this
has gone the .SQL of "LocalQuery" gets reset.

 

[Jon Ley]

David,

Thanks for your suggestion. For some reason I was having trouble removing
this temporary query at the end of the process (running DB.QueryDefs.Delete
"tmpQry" and DB.QueryDefs.Refresh didn't get rid of it), so I finally settled
on deleting and recreating the LocalQuery at the start of the procedure and
this looks to be working fine.

Regards,

Jon.