Naturally. Just remember to clean up any legacy issues in the table beforehand.
If such a parameter table is used excessively, there is also the possibility that the database will be bloated => outsourcing of the real table to a temporary local backend.
Are you using a list box with multiple selections in the form for a selection? This can only be evaluated using VBA. How accurate is your evaluation?
There are several ways to use a multiple selection in a filter, for example ...
SQL:
WHERE FieldA IN ("A", "C", "E")
WHERE Instr(1, "|A|C|E|", "|" & FieldA & "|") > 0
WHERE FieldA IN (SELECT Param FROM ParameterTable)
It is certainly understandable that you have to formulate your selection in such a way that it can be used and function in the selected filter variant.
With an existing string array, variant 2 could be used.
Code:
Public Function MyChoice() As String
MyChoice = "|" & Join(YourStringArray, "|") & "|"
End function
'------------------------------------------------
WHERE Instr(1, MyChoice(), "|" & FieldA & "|") > 0
In serious database environments, value will be placed on performance, even with larger amounts of data, and it is essential to enable index use there. These now offer variants 1 and 3, but not variant 2.
Additional aspect: An OR link of the multiple selection was considered here. With an AND link, things get a little more complex.
The WHERE FieldA IN (SELECT Param FROM ParameterTable)[/CODE] was the most efficient and simplest solution to what I needed. I make a table from the ListBox.ItemsSelected and use the table in the Query exactly as listed here. I delete the table after the query has run. Marvellous. Thanks so much for the help.
The WHERE FieldA IN (SELECT Param FROM ParameterTable)[/CODE] was the most efficient and simplest solution to what I needed. I make a table from the ListBox.ItemsSelected and use the table in the Query exactly as listed here. I delete the table after the query has run. Marvellous. Thanks so much for the help.
Affirmative on Gasman's comment. Too many table add/delete operations will cause bloat. Actually, INSERT/DELETE operations will also cause some degree of bloat. However, if these people who go into the field were to make a new copy of the master FE for travel use with each visit and THEN reload what needed to be reloaded, you could put a blank table in the COPY as part of the "preparing to go on the trip" sequence. In which case the new copy would have minimum bloat and you would just discard the data with each new prep step. The table would be local to the FE and thus easily accessible.
Affirmative on Gasman's comment. Too many table add/delete operations will cause bloat. Actually, INSERT/DELETE operations will also cause some degree of bloat. However, if these people who go into the field were to make a new copy of the master FE for travel use with each visit and THEN reload what needed to be reloaded, you could put a blank table in the COPY as part of the "preparing to go on the trip" sequence. In which case the new copy would have minimum bloat and you would just discard the data with each new prep step. The table would be local to the FE and thus easily accessible.
