Any Excel / Access query gurus?

[Participant]

Calling anyone that queries external data via MS Access! Bit of a long shot...

I have a query in Access that looks at our accounting program and retrieves a work in progress valuation. I have also created a report based on that query. I am now trying to link the query to Excel so that I don't have to keep manually exporting it whenever I run it.

I have another query set up in a very similar way, with two tables linked, which has no problems pulling the data into Excel. Both queries produce about 8000 lines.

I have two issues:

- The report itself in Access produces data fine and takes about five minutes to run. When however I run the query on which the report is based, which should give me exactly the same information as the report, it never completes and just hangs Access.

- When I link the Access query to Excel, it takes five minutes to finish just like the report, but then produces zero information, only the query fields as the headings, like this:

This makes me think something is wrong with my query...



...yet the report works fine.

Any ideas anyone? Many thanks.

 

[Participant]

Thanks. Yep I think I need some specialist knowledge for this, will take a look at that site. Cheers.

 

[Participant]

Thanks for the reply, but I fixed it by deleting all existing connections in the spreadsheet and reimporting my queries, for anyone that stumbles across this thread with the same problem. God knows why different connections were affecting unrelated queries in the same workbook.

For interest's sake, I'm bringing the data via ODBC from my accounting software. The index tip is something I need to look into as so far I've only indexed the primary key.

The report applies filtering but no sorting or grouping as this is done in Excel.

Many thanks.