Change SQL Server Pass Through Query Variable

Question

So I have a SQL Server pass through query in MS-Access that I created with the query design. I just double click it and it runs and opens up in datasheet view. Then I can export it. The query looks like so:

DECLARE @acyr AS varchar(4);
 SELECT @acyr = '2018'

SELECT *
FROM Table
WHERE year = @acyr

I also have a form with a listBox labeled acyrList where the user picks from 2017, 2018, 2019. I would like for acyrList.Value to be passed to the @acyr in the pass through query and return the records for that year.

How can I achieve this ?

Jim B · Accepted Answer · 2019-08-02 18:19:46Z

2

You can rebuild the SQL in the Click event for the listbox and then assign that SQL to the pass through query. Something like:

Private Sub acyrList_Click()
  Dim strSQL As String
  strSQL = "DECLARE @acyr AS varchar(4); " & _
           "SELECT @acyr = '" & acyrList.value & "'; " & _
           "SELECT * From Table WHERE year = @acyr;"

  'MsgBox strSQL
  CurrentDb.QueryDefs("passthrough_query_name").sql = strSQL
End Sub

answered Aug 2, 2019 at 18:19

Jim B

4594 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change SQL Server Pass Through Query Variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related