I'd like to thank everyone who has contributed to this thread, and I thought I should bring you up to date.
The existing application using both an Access front end and Access back end has always worked well, even with roughly 100 users. It has become quite a large system with hundreds of forms and reports, many hundreds of queries and over 100 data tables, some with close to a million records. Despite that, it works fine, the system is fast enough, and the client is happy. However once the back end exceeded a GB in size we decided to convert the back end to SQL Server, and the rest, as they say, is history.
Unfortunately, try as we might, we could never get the speed of the most crucial part of the system to be fast enough.Thanks to the input from various contributors we have managed to get the response time down, on the fastest of 3 servers, to just about an acceptable level, but this is very borderline and we have no idea what effect 100 simultaneous users would have - as we can't test that.
And so we have taken a decision which I'm sure will surprise a lot of you, and it's this:- we are going to keep the existing system, but on a monthly basis we are going to archive data to keep the back end down to a manageable size. Archived files will be stored in such a way that they can be quickly and seemlessly restored with the users hardly noticing the short delay, if any. You can guess where the archive will be stored, yes, on the SQL server naturally.
As a rough guide we aim to keep the back end to below a Gig, and reckon that only 1 in a thousand requests will require pulling a record from the archive.
So that's the plan. If anyone would like to comment then please do. We may well return to this issue but for the time being we see this as the best approach in the short/medium term.