ODBC Query very slow when using join but not when direct

[th081]

Hi,

I have a make table ODBC linked query, two tables (ODBC table) and a local table are linked by the date field in the local table the second field in the local table is the Month and year as below:

Apr-15 01/04/15
Apr-15 02/04/15
......
......
Apr-15 30/04/15

The macro launches a combo box and the user selects Apr-15 it then starts the make table query so that Apr-15 is one of the inputs and it querys the ODBC table for the 30 days in April that are listed in the local table. The issue is that all of a sudden this query has got very slow from a few mins to ~60mins. I have recreated the query in a separate database (new tables etc) but it is still very slow, this has just happened as last month it was ok. Interestingly if I remove the second table and enter the parameters manual i.e. between >=[start date] and <= [end date] the speed is back to normal. Does anyone have any clue why the same information returned (albeit requested in slightly different ways) could have such a difference in query times.

Could something have changed on the backend db and what might cause these diffs?

Regards

 

[svendiamond]

I would first ask have you thought about using SQL to join the tables you want and then querying the new table in an Excel sheet, then simply looking up the data from the sheet?