Results 1 to 6 of 6
  1. #1
    pme4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    5

    Complex Dynamic search including IN statements

    G'day All,

    I am transitioning a complex Access solution to having an Azure SQL back end. One challenge I have at the moment is a complex search form. This form allows users to search records by selecting any combination of from over 20 fields in the data structure. In Access I build a Where statement programmatically based on what is selected and it works great, but performance is horrendous with the SQL back end. So I have been experimenting with Stored Procedures and Pass Through Queries.



    With the stored procedures, I have been pulling down all records that the user is allowed to access and then apply a filter to the record. This is super fast, but, 6 of the fields use an IN statement as the user can select multiple options. I can't find a way to apply an IN type operator on an ADO recordset.

    I've looked at the pass through query option, but the dynamic nature of the where statement would require a very complex query on the SQL Server side. I have not found a way to use an IN statement that is dynamic that does not use another SQL query to find the values.

    Can anyone provide some guidance on how best to handle this requirement?

    Thanks
    (Form below)

    Click image for larger version. 

Name:	Search Form.PNG 
Views:	25 
Size:	57.3 KB 
ID:	49387

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I have no problem using IN() with ADODB.Recordset object in VBA.

    An alternative may be to save foreign keys to a temp table then INNER JOIN on key fields. Might not be practical with 6 tables.

    VBA and QueryDefs could modify query object SQL statement.

    Have you tried applying WHERE criteria to form Filter property? Or using WHERE CONDITION argument of OpenForm/OpenReport methods?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pme4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    5
    Thanks for the quick response June7.

    The output is to Excel in this case, so I need to modify the recordset directly before passing it to Excel, I do use the form filter option for some of the other search functions where the results are displayed in a table.

    I have contemplated writing the initial results to a local table and then using a query against that, it will probably be quicker than querying the backend directly.

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    use a SQL procedure with dynamic SQL (exec sp_executesql) and pass the IN parameters as a string?

  5. #5
    pme4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    5
    G'day NoellaG,

    From what I can find, the requirement for passing in parameters for an IN statement in a Stored Procedure is that each value has to be passed separately and then concatenated in the Stored Procedure, so would have to define a variable for each potential value. This is challenging there there are 6 potential IN statements and the number of values can't be predicted. Am I missing something in a solution?

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, using dynamic SQL means building the SQL statement as a string and then execute the SQL statement using sp_executesql. So in this case you can pass all parameters at once in a string. Pass the in parameters in one string to the procedure and build a sql statement as a string in the procedure.

    https://www.sqlshack.com/introductio...with-examples/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-15-2019, 05:54 PM
  2. Replies: 5
    Last Post: 11-30-2014, 08:12 PM
  3. Replies: 18
    Last Post: 01-31-2013, 01:18 PM
  4. Complex search through strings in records
    By pkstormy in forum Code Repository
    Replies: 2
    Last Post: 03-17-2012, 11:25 AM
  5. Complex Search Criteria
    By dutrac6835 in forum Queries
    Replies: 8
    Last Post: 06-23-2011, 04:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums