0

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'
BitAccesser
  • 719
  • 4
  • 14
Caio Gomes
  • 681
  • 1
  • 11
  • 23
  • You mean a [parameter query](https://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update)? A [passthrough query](https://support.office.com/en-us/article/Process-SQL-on-a-database-server-by-using-a-pass-through-query-B775AC23-8A6B-49B2-82E2-6DAC62532A42) is something different. – BitAccesser Sep 12 '17 at 21:43

1 Answers1

0

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Hi Albert, what I want is: build a form where I enter the variable and it returns me this query result and, also, uses the first element of the query as source to select a value in another table. Do you think that is possible? – Caio Gomes Sep 18 '17 at 02:27