Excel 2007 - Using Microsoft Query

I started to use the Data Connection tool using Microsoft Query to access data directly from SQL tables to update spreadsheets. The feature is great as I can link to several tables. I have a data connection in an Excel spreadsheet that updates payments made within a date range entered on the Excel spreadsheet. The query seems to take too long to refresh the data - like 5 minutes. Staff not happy waiting. Is there anything I can do to speed up the query/refreshing of data?

Thank you much.

Check your query, if you run the query in a sql editor, if it is the same amount of time, you need a better query because your DB is doing a lot of work to get you your info. If the SQL editor spits it out in seconds, then you need to write your macro doing the sql connection because that one is inefficient.

I will give it a try. Thank you much.

Hello SLX - I did run the query in SQL and it runs in seconds. Does that mean settings in Excel or data connection using MS Query need to be changed?

yes, you will either tweak the setting in ms query to try and get an improvement, or you will have to use a macro that uses a different connection to speed it up.

One thing you can try, is to run the ms query in a brand new workbook. If it runs a lot faster then your current workbook. Then you have multiple queries in your current workbook and that is slowing it down

Thanks so much for your help. Much appreciated.