Building/Storing a passthrough:How to assign connection string andmake passthrough

[Phil Smith]

I know the connection string I wish to apply, and have the SQL ready
query, I just don't know how to put it. This stores it as a basic
query. What do I do to store it as a passthrough with Cnnect as as the
connect string?
==========================
Dim Q As QueryDef, DB As Database
Dim Selectpiece As String
Dim SQLSTRING As String
Dim Cnnect as String
Cnnect = "ODBC;DSN=pavinbase;"
SQLSTRING = "SELECT accounting.post_table, accounting.post_date,
accounting.post_process, accounting.post_id "
SQLSTRING = SQLSTRING & "FROM accounting "
SQLSTRING = SQLSTRING & "WHERE accounting.post_table=" & Chr(34) &
"fg_order_h" & Chr(34) & " AND accounting.post_date>="
SQLSTRING = SQLSTRING & Chr(34) & Format(DateAdd("d", -180, Now()),
"yyyy-mm-dd hh:mm:ss") & Chr(34)
SQLSTRING = SQLSTRING & " AND accounting.post_process=" & Chr(34) &
"WIP Receive FG" & Chr(34)
'Store the Query
Set DB = CurrentDb()
Set Q = DB.QueryDefs("FGDetail")
Q.SQL = SQLSTRING
Q.Close
==========================================
Phil

 

[Duane Hookom]

I just create a pass-through query and then use a little DAO code to set the
SQL property (and the connection property if necessary)
Your Dim's should be
Dim Q As DAO.queryDef, DB As DAO.Database

Set DB = CurrentDb()
Set Q = DB.QueryDefs("FGDetail")
Q.SQL = SQLSTRING
Q.Connect = Cnnect
Q.Close

 

[Phil Smith]

The Connect string helps, but bot sure about the passthrough. You are
saying basicly create my "placeholder" query as a passthrough, and it
will stay a passthrough when I update the SQL?

 

[Duane Hookom]

"will stay a passthrough when I update the SQL?"
Yes. I do this regularly.