Upgrading your Access Application for a Multi-user Environment - Performance Improvements
(Page 5 of 5 )
One of Microsoft’s recommendations for improved performance across a network is database-splitting. With database splitting, static data is left on the client’s machine and changeable data is moved to a network drive. What this amounts to is creating a new database on a network drive and moving all tables into this database. Each client database will hold queries, forms, VBA code and reports with links to the tables on the shared network drive. This will reduce network traffic and speed up queries and reports. It will also reduce contention for any temporary objects created. This technique is useful regardless of whether you developed your application using a VB front-end or using only Access. In some instances it may also make sense to keep tables on the client rather than move them to the networked drive. For instance, if you have a look-up table of city names that rarely changes, it might make sense to keep this table in each client database.
If you do decide to go this route, Microsoft has made it very easy by creating a database utility to perform this function. Before proceeding though you will want to make use of another wizard – by analyzing performance under the “Tools” menu option. This tool will examine your database and come up with suggestions for improvements. Most important are the creation of additional indices. In a networked environment this will help to reduce network traffic.
So far we have concentrated on server-side dynamic cursors and the accompanying concurrency issues. However, for the sake of performance we should also use static, client-side cursors whenever appropriate – when running reports for instance.
You can also compile your database using the menu option to create a MDE file. This option really only makes sense if you are using Visual Basic for Applications code in an Access based application and has more bearing on security than performance.
Other Issues
Implementation of error trapping in a multi-user environment also requires special attention. For example, provision should be made for retrying updates that fail due to conflict over resources. However, providing a thorough review of error trapping is perhaps the subject for another article.
You will also want to encrypt your database. This may have already happened depending upon which wizards you have used to this point. If not, it is a fairly straightforward matter of using the correct menu option.
Finally, if you have created your application using VB, then you will want to deploy it using the Package & Deployment Wizard that comes with Visual Studio.
Conclusion
This article has taken you through the major steps necessary to upgrade your desktop Access database application for a multi-user environment. Concurrency and security issues were covered as were performance improvement suggestions. With the right approach, an Access database can function satisfactorily in a multi-user environment.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 33 
