0

Creating a query to be used by front-end users so I have created an Access form with a simple keyword search box and search button. The linked table that is being searched is very big, (Millions of rows of data). When running the query through normal access design view, a normal query will execute in 30-40 seconds. Long but manageable, when running the query via the form, (button uses macro to execute with unbound textbox variable), the query takes 5-7 minutes. This is too long so I figured I would use a TSQL query (pass-through connection) which would be much faster. Anyone know why the form would be giving me trouble?

However, I am having trouble referencing access form objects in said TSQL query. Is this even possible?

Normal Access SQL query:

SELECT dbo_SEARCH_TRACKING.KEYWORD, Count(dbo_SEARCH_TRACKING.IP_ADDRESS) AS SEARCHES
FROM dbo_SEARCH_TRACKING
WHERE (((dbo_SEARCH_TRACKING.IP_ADDRESS) Not In ('198.73.28.2','198.73.29.2','63.240.175.30')))
GROUP BY dbo_SEARCH_TRACKING.KEYWORD
HAVING (((dbo_SEARCH_TRACKING.KEYWORD) Like "*" & [Forms]![KeywordSearch]![keywordsearch1] 
& "*" And (dbo_SEARCH_TRACKING.KEYWORD) Like "*" & [Forms]![KeywordSearch]![keywordsearch2] 
& "*" And (dbo_SEARCH_TRACKING.KEYWORD) Like "*" & [Forms]![KeywordSearch]![keywordsearch3] 
& "*" And (dbo_SEARCH_TRACKING.KEYWORD) Like "*" & [Forms]![KeywordSearch]![keywordsearch4] & "*"))
ORDER BY Count(dbo_SEARCH_TRACKING.IP_ADDRESS) DESC;

My attempt with a TQSL query:

SELECT dbo.SEARCH_TRACKING.KEYWORD
    ,Count(dbo.SEARCH_TRACKING.IP_ADDRESS) AS SEARCHES 
FROM dbo.SEARCH_TRACKING 
WHERE (((dbo.SEARCH_TRACKING.IP_ADDRESS) Not In ('198.73.28.2','198.73.29.2','63.240.175.30'))) 
GROUP BY dbo.SEARCH_TRACKING.KEYWORD 
HAVING (((dbo.SEARCH_TRACKING.KEYWORD) LIKE '%' + @keywordsearch1 + '%' 
    AND (dbo.SEARCH_TRACKING.KEYWORD) LIKE '%' + @keywordsearch2 + '%' 
    AND (dbo.SEARCH_TRACKING.KEYWORD) LIKE '%' + @keywordsearch3 + '%' 
    AND (dbo.SEARCH_TRACKING.KEYWORD) LIKE '%' + @keywordsearch4 + '%')) 
ORDER BY Count(dbo.SEARCH_TRACKING.IP_ADDRESS) DESC

Please help. :)

Nate Logan
  • 1
  • 1
  • In the button code, are you replacing the @keywordsearch1..4 parts of the string with values from the respective form controls? What trouble are you having? – tinazmu Jul 19 '22 at 01:26
  • What is the backend database? – June7 Jul 19 '22 at 02:41
  • 1
    Does this answer your question? [Pass through query with userinput in access form](https://stackoverflow.com/questions/36982883/pass-through-query-with-userinput-in-access-form) or https://stackoverflow.com/questions/46183595/how-to-use-access-pass-through-query-in-a-form – June7 Jul 19 '22 at 02:46

0 Answers0