Microsoft Query Hangs when adding table

[Guy Parker]

Hi,

I have a problem with Sage Link to Excel and MSQuery

I open the Excel Query Wizard and Add columns from the INVOICE table
I then edit the query in Microsoft Query

In Microsoft Query I press the 'Add Table(s)' button and select the INVOICE_ITEM table.

When I press the 'Add' button MSquery hangs.

It appears to only hang when selecting the INVOICE and INVOICE_ITEM tables.

Is it because of some relationship between these tables?

I am running Sage Line 50 Financial Controller V11.01.0030

[brucedenney]

The operative word in your question is "appears", I suspect that you already have an idea that it is taking just a long time, which is what I also suspect is happening.

What you are doing is adding an unlinked table, so as there is no link every record in the second table is included for each record in the first table.

Say you have 100 invoices and each one has 10 items so you have 1000 items. This is a very small number but it results in 100,000 records returned!

I think that query is running fine, it is just going to take ages to run because there is so much data.

What you need to do is to link the tables before you execute the query.

To do this you need to turn off auto query before you add the second table, create the link between the two table and then either manually refresh the query or switch auto query back on.

I like to leave auto query switched off.

(there is a button for auto query on the main toolbar in MS Query, it is the exclamation mark with the circular arrows)

[Guy Parker]

Hi Bruce,

You were right - it wasn't hanging - it was just taking a very long time.

Turning off auto query in MS Query did the trick.

Thanks