Slow Access Query Performance when connected from Excel

Question

Hi Everyone-

I have created an Access Database which is has linked tables to spreadsheets. The queries in the Access Database are accessed from a report in .xlsx format. 

Previously we were running the entire setup on a local machine and it was working fine. Recently we moved the entire setup of Access Database, linked data sheets and the front end excel report on a network and the performance has been dropped significantly.

We the entire system on network to be able to work collaboratively between various team members. 

Any recommendations or suggestions would be greatly appreciated.

Answer 1

Access tables can (and should be!) indexed and optimized for performance. Excel spreadsheets cannot. Excel is NOT the same as Access, and doesn't work the same! Working on a single computer can (it appears) be fast enough to overcome the disadvantage, but moving the data across a network, updating cells in a second computer from your computer, and (worst) sharing a single instance of Excel between different users is another story.

What's the source of the data in the sheets? Would it be possible to make a (properly designed and optimized!) Access database the primary data repository and either link to it from Excel, or periodically export the data to Excel if spreadsheet operations are needed?