Pass Through Queries and Parameters

[Kenln]

Can you use a parameter in a Pass-Through query.

If so how. I can seem to get the syntax working?

Thank you for any help,

 

[Kenln]

Or... Can I define an additional where is code when I open it?

 

[KeithG]

No you can not use Parameters in a PassThrough query. What I do with my PT quieries is create a VBA function and a form to retrieve the parameter and create the SQL at runtime.

 

[Bodisathva]

store your SQL as text, and block out your "parameters" within the string using a pipe delimiter along with identifying text. Use the Replace function to replace place holders with appropriate variable data.

strSQL = "SELECT * FROM table1 WHERE field1 = |targetF1| AND _
                                     field2 = '|targetF2|'"

strSQL = Replace(strSQL, "|targetF1|", intF1)
strSQL = Replace(strSQL, "|targetF2|", strF2)

 

[modest]

strSQL = "SELECT * FROM table1 WHERE field1 = |targetF1| AND _
                                     field2 = '|targetF2|'"

strSQL = Replace(strSQL, "|targetF1|", intF1)
strSQL = Replace(strSQL, "|targetF2|", strF2)

Calling two replace functions, is a waste of proc cycles and memory. Why not just do this to begin with:

strSQL = "SELECT * " & _
         "FROM table1 " & _
         "WHERE " & _
            "field1 = "  & intF1 & " AND " & _
            "field2 = '" & strF2 & "'"

 

[modest]

Where are you trying to pass parameters to?

Dim qry As DAO.QueryDef

Set qry = ""
qry.SQL = ""
qry.Parameter = ""

 

[Kenln]

Modest, I am trying to pass parameters to a Pass Through query. I do not see how to build the parameter question in the SQL statement. I know how to build a SQL statement in VBA and I just got the VBA (ADO version) working to open the recordset I want with the variable I need. But... I am still new at this and to accomplish what I need in full may be over my head.

I have two tables which I need to linked together and sum (Group by). This is what I have just got working. Cool!

I also have two other tables that I need linked together. I don't see this as a problem either.



HOWEVER, then I need to link the result of these two different queries together. Umm, I'm lost. If this was in Query graphic design it would be easy, build two different queries then build a third linking the first two.

But to do this in code. As I said I can build each of the first two queries in code but then to link those together I don't even know where to begin.

 

[Bodisathva]

Calling two replace functions, is a waste of proc cycles and memory. Why not just do this to begin with:

Because I don't know how large the actual SQL statement is, how many parameters are involved, or how many other programmers are involved.

I would thank you for your kind words, but, never mind...I'll just be over here wasting time and memory.

 

[Kenln]

Thanks for your help guys.

Definitely heading in a better direction. One step closer.

I decided to create two temporary tables then create another query using them. Unless someone knows how to use two Select Queries in the From part of another Select query.

 

[Bodisathva]

Unless someone knows how to use two Select Queries in the From part of another Select query.

SELECT field1 
  FROM table1 
 WHERE field2 = (SELECT x 
                   FROM table3 
                  WHERE [Criteria]) 
    OR field3 = (SELECT x 
                   FROM table4 
                  WHERE [Criteria])

Alternatively, if you are checking the same field from both of the nested SELECT statements, you could also use IN:

SELECT field1 
  FROM table1 
WHERE field2 IN((SELECT x 
                   FROM table3 
                 WHERE [Criteria]), 
                 (SELECT x 
                    FROM table4 
                  WHERE [Criteria]), ...)

 

[Kenln]

I think what I am looking for is more like.

Select Field1, Field2, Field3, Field 4
From

(Select 
Field1, Field2, Field3, Field 4 
From A Right Join B On Field5 = Field5
)

Right Join

(Select 
Field1, Field2, Sum(Field3), Sum(Field4) 
From C Right Join D On Field1 = Field1 
Where Field6 = X 
Group by Field1, Field2
)

On
???????????

But I can't get it working... From what I have read it should work (unless you can advise me otherwise), and if so I'm definately doing something wrong.

 

[Kenln]

I got it working... I had extra () in the From statement.

This is great and completly solves my Parameter problem.

I could not have done it with out your help and patience.

Many Thanks