best practices for multi-user environment & large data amounts
Posted on 2008-10-28
A year ago I started working in a small company that develops and sells an MS-Access 2000 application. This product deals with accounting and bookkeeping, inventory management, CRM, sales analysis, balancing, expenses, and so on and on and on. It took 7 years to develop. The developers are gone and I'm in charge for improvements, bug fixing and developing new ideas.
Most of our Clients are small retail shops or self-employed people. But there are some that are small or large companies as well. That's where the problem begins.
Every new year our clients archive their data and start a new database. Despite this fact, One of them has a huge database of 200 Mb, with 290 tables some of them contain 150000 records within a single year.
The problem is: slowness!!
We've done everything we know that might improve their application-performance: our app is well split into backend db & a few frontend apps, the design is professional, the right fields are indexed, where we need too complicated queries for reports we use local/temp tables, each user has his own copy of the application & but still it is slow!
This is a large application: for example, the business-module consists of 666 forms, 300 reports, 509 queries and 11 modules.
Some people said to me: SQL server is the solution! Is it really?
I know nearly nothing about SQL server. In the past 9 years I worked entirely with Jet access applications.
Is it realistic to convert that complicated 7-year-development-application to SQL server.
And in the end is it really going to work faster?
If that's so what's best? To link to SQL server tables or to create an ADP?
What are the best books in the world that teach SQL server for access developers?
If SQL server is not such a great solution, what are the best practices or even the hidden secrets of making Jet Access apps work fast in a multiuser-environment & huge amounts of data?
(some more details: OS is win XP sp2. We use access 2000 because the app uses conditional-formatting in forms extensively, and Access 2003 has a bug with cond. formatting, as you surely know. Access 2007 hangs when we try opening/converting our staff to ACC2007, for a still unknown reason. So here we are, still with ACC2000. Our clients use it with the Access-Runtime. Number of users: 15.Network: simple file sharing network, and 2 users are sitted far away, connecting to their own copies of the app through a terminal-server).
Thank you in advance for your attention to my question,