Over the last ten years I've developed a rather comprehensive medical office application in Access 97. There is a front end mdb on each workstation and a back end mdb on a windows 2003 Server. Over the last six months the performance of this application has become progressively slow. I believe this is due in part to a big increase in the number of users (25%) as well as a large increase in business (i.e. more patients, transactions etc). It should be noted that the application performs nicely as long as there are just a few users (8-10). The degraded performance always occurs when 12-18 users are accessing the application simultaneously. I haven't performed any actual tests but I suspect that some of the queries are moving a lot of data across the network. This in turn is slowing down the application as well as other network related tasks such as opening documents on the server.
I think it might be helpful for you to know some specifics about the two main tables:
The transaction table contains 271,042 records. This table has 22 fields.
The Patient table contains 29,429 records. This table has 86 fields.
I really need to come up with a solution that will speed things up. I would prefer to keep the front end application on Access 97 and not rewrite it all. The client is amenable to a SQL server solution. I'm wondering about the most expedient way to increase the performance:
Should I simply move all the back end tables to SQL server and link to them via an odbc connection in Access?
Should I use passthrough queries?
Should I create views on the SQL server and access them from the front end?
Would simply upgrading to MS Access 2010 increase performance?
Thank you in advance!