How to use access pass-through query in a form

Question

I'm using pass-through query code below on access. The code isn't a problem. My problem is to make a form on access that enters the varible on the place indicated and returns me the table from que SQL select. The regular solution: [Form]![Variable] doesn't work, because pass-through query doesn't support it I imagine (I'm not an Access expert). Does anyone have a solution for this problem?

SELECT instalacao
    ,tipounidade
FROM sgdprd.useccionadora us
INNER JOIN (
    SELECT bloco
    FROM sgdprd.redeprimaria rp
    WHERE rp.useccionadora IS NOT NULL CONNECT BY rp.nox = PRIOR rp.fontex
        AND rp.noy = PRIOR rp.fontey START
    WITH rp.utransformadora = (
            SELECT utransformadora
            FROM sgdprd.redeprimaria rp
            INNER JOIN sgdprd.consumidor con ON rp.utransformadora = con.instalacao
            WHERE con.conta = '**VARIABLE GOES HERE**'
            )
    ) lista ON lista.bloco = us.instalacao
WHERE us.tipounidade = 'DJ'
    OR us.tipounidade = 'RL'

Answer 1

You don’t mention what you want to do with the results of this query?

Any pass-through query is read only.

However, you can use the following code:

Dim strSQL As String

strSQL = "your sql goes here where con.contra='[P1]' bla bla"

strSQL = Replace(strSQL, "[P1]", Me.SomeControl)

With CurrentDb.QueryDefs("QryPass")
   .SQL = strSQL
End With

It is assumed that me.SomeControl in the form is say restricted to a number column since if it is a free form text box, then you are open to sql injection. QryPass is simply a saved pass-though query and you can re-use that query over and over in code for any T-sql or server side commands you wish.

As noted, you have to share additional information as to what you want to do with the pass-through query (such as assign to a recordset, combo box, report or even perhaps the forms recordsource - so addtitional info is needed to complete this problem, but the above provides a working example.