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)