Any Excel / Access query gurus?

Participant · 26 May 2010 at 11:32

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.

Namsnik · 26 May 2010 at 20:45

If no help here try www.tek-tips.com excellent, helpful site.

Participant · 26 May 2010 at 21:01

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

blueboy2001 · 26 May 2010 at 22:19

Try splitting it into two queries. The first one should contain the join and the criteria for the joined field, and output the fields you need. Then create a second query based on the first one and add the extra criteria on the other fields.

Are the tables in Access, or are you bringing them in via ODBC direct from your accounting software? If they're in Access make sure you've got an index set on each field your applying criteria, this will speed things up.

Does the report apply some sort of aggregation or grouping to the query?

Participant · 15 Jun 2010 at 09:30

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.