Forum Discussion
Very slow performance trying to edit a Microsoft Query in Excel
Pulling data into Excel using MS Query from an ODBC DSN, the query hangs for 20 to 30 minutes every time I try to make a change to a parameter. This is on a PC of a user I am trying to help, and I'm ...
This is impossible to answer without more detailed information about the query , the ODBC driver, the type of database system and the type of connection to the database system I'm afraid.
- Thanks Jan,
This is a client/server ERP system called Sage 100 Advanced (formerly known as MAS 200). Windows 2016 server, win 10 workstation. Trying to pull data from 2 tables into Excel using MS Query. The tables are AR_InvoiceHistoryDetail and AR_InvoiceHistoryHeader. There are a several hundred thousand records, so I want to filter only for a date range - say July of this year (once I get this working, I will try to put in a prompt so the user can change the date range at runtime). I have set up a "Silent DSN" so we don't have to keep logging into the database. The connection works fine, but, as I say, every time I try to edit the query to filter for things like a specific item type or date range - MS Query just hangs there for about half an hour. Eventually it returns data, but it returns the entire data set, ignoring my date selection and item type selection.
Please let me know if you would like any more info.- Perhaps it is better to just edit the query's SQL directly rather that going through MSQuery? If you open the Queries & connections pane and click on the connections tab, you should be able to right-click the connection in question and select "Properties". You'll find the SQL on the Definition tab.
