Welsh Corgi (6/27/2011)
repicurus (6/27/2011)
I've had extensive experience using this development model: ACCESS 2003 front-end --> SQL 2005 back-end.It actually performs very well if both sides are done properly.
Unless you did something really REALLY bad in SQL, it's likely a front-end issue.
How many concurrent users did you have?
What was the size of your database and tables?
In our FL office, hitting the same main operational SQL Server (2-node cluster):
575+ users: all of whom were using the "Paperless Sales Entry/Tracking System" ACCESS app, and 2 more ACCESS apps for Verifications.
Numerous SSRS reports, SSIS packages, and SQL Server Agent jobs running 'round the clock.
Client Web Portals - mixed bag of classic VBS/ASP web apps and increasingly more and more C#/ASP.NET apps.
In CA and NV offices, similar environment but with about half the users and running less hours each day.
We had many databases, including one for main telephony system.
Largest database was approaching 500 GIGS; smallest - 100 MEGS (?); median size - about 5 to 7 GIGS as I recall.
Tables, especially telephony history had over 50 million rows, and archive history about 120 million.
Table sizes used by ACCESS apps ranged from several hundred to millions of rows.
Data entry/update FORMS were designed to select one record or limited group and were mostly UNBOUND.
Search front-ends would put an empty FORM and prompt for search parameters with 1 to 2 arguments required prior to parsing a query string and issuing against the server.
It was a call-center, which is a bit like any "normal high transaction volume" business but on crack. 😉