This is bugging me.
I use Access as a front end to SQL and I often have people asking me for datasets from the various databases under my control.
One method I use, is to link two tables togther. One containing my criteria and the other the data. Seems quicker this way.
So I pull back my data by running the stored procedure. This can vary from 1 record to 50,000+ which usually needs copying to Excel. 20 + columns.
My problem begins when I need to copy the data. If I select all records and copy, it's quick. Scrolling through the data pulled down is also quick with no lag. However sometimes I may have to split the data as it's too much for one sheet of excel and will sort on a field within access.
As soon as I do this, my cpu hits 100% and it takes ages to sort. Once done, scrolling through the data is very very slow. Lagging behing my movement and copying the sorted data takes 5 - 10 minutes. A process that took a few seconds when unsorted.
Any idea what the hell is going on? I can take the same no of records and import them into a local access db and it behaves totally different. IE sorting and copying is instant.
The SQL server sits on the end of a wan but as the data has been pulled down to the local machine, I'm a little confused as to why it behaves like this.
Help please!
I use Access as a front end to SQL and I often have people asking me for datasets from the various databases under my control.
One method I use, is to link two tables togther. One containing my criteria and the other the data. Seems quicker this way.
So I pull back my data by running the stored procedure. This can vary from 1 record to 50,000+ which usually needs copying to Excel. 20 + columns.
My problem begins when I need to copy the data. If I select all records and copy, it's quick. Scrolling through the data pulled down is also quick with no lag. However sometimes I may have to split the data as it's too much for one sheet of excel and will sort on a field within access.
As soon as I do this, my cpu hits 100% and it takes ages to sort. Once done, scrolling through the data is very very slow. Lagging behing my movement and copying the sorted data takes 5 - 10 minutes. A process that took a few seconds when unsorted.
Any idea what the hell is going on? I can take the same no of records and import them into a local access db and it behaves totally different. IE sorting and copying is instant.
The SQL server sits on the end of a wan but as the data has been pulled down to the local machine, I'm a little confused as to why it behaves like this.
Help please!