This week a coworker and I were discussing Access design practices that can be used to improve performance and security. I got to thinking about the things he suggested and that I've picked up over time (from Microsoft and other developers) and realized that I could benefit from a review and, in some cases an update of those practices. So for anyone that might be helped by it, here's what I sifted out:
A split architecture has long been suggested and I have used it and found it improves performance, minimizes collisions and corruption, and makes support and program enhancements so much easier.
Because the front-end, with forms, reports, queries and VBA, is on the users local desktop network traffic and load time is reduced. Support is easier and more efficient because the tables are stored in a separate database. When you make any form, report, query and/or vba modifications you simply replace the users front-end file without affecting their data. Of course in the, hopefully, rare event that you find it necessary to adjust a table struture, you can do it with users offline as before.
How to split an application:
There are a couple of ways to accomplish a split application but the easiest is to use the wizard Microsoft has included. From the "Database Tools" menu option, select "Access Database" in the "Move Data" section:
Your tables will be exported to the location you select (using UNC rather than mapped paths is recommended) with links included from the front end. If you move your back-end database to a different location, you will need to use the "Linked Table Manager" (under the "External Data" menu option) to re-link your tables. Now if you give users on the network a copy of your front-end and they have access to the back end location, they will be set to go.
Establish a Persistent Connection:
In addition to the above, by keeping your back-end linked tables open with a persistent connection you improve speed. This avoids creating a new lock file (ldb) on the database each time a tables is used.
Here's a quote from Microsoft with simple instructions:
"You can greatly enhance performance, when opening the main database and opening tables and forms, by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then, use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and from creating and deleting the associated .ldb file."
Save Database as an accde:
An Access 2010 database saved as an ACCDE makes it an executable file, gives a smaller file size and locks your database (first disable shift key startup and make sure you have a back up). To save the Access 2010 database in ACCDE format, first save a back up for development, open the database and on "File" menu, click "Save & Publish," select ACCDE.
Password Protect VBA Code:
To keep zealous, curious users from accessing (and possibly changing) your VBA code, password protect it. Here's how to do it: While in the VBA editor, click on "Tools" on the Menu Bar, select your database name properties, select the "Protection" tab, select "Lock project for viewing," fill in the "Password" and "Confirm Password" fields. Please BE SURE YOU HAVE SAVED YOUR PASSWORD SOMEWHERE!!
MS Access 2010 Runtime:
Microsoft has kindly provided us with a free runtime (no it does not expire) that we can distribute to users that do not have Access 2010 installed on their desktops. Imagine, they don't have to buy Office! The application should be tested in runtime mode before deploying to verify that all needed features are working since the runtime disables some features. To test in runtime mode, add the /runtime switch to the run line of your shortcut, or rename the file extension from accdb to accdr (thank you Antoine). If you find any lack you can program in the needed functionality before deploying.
To choose the correct runtime, right click COMPUTER to see if your operating system is 32 or 64 bit. System requirements for MS Access 2010 Runtime are:
Windows 7, Windows Server 2003 R2 (32-Bit x86), Windows Server 2003 R2 x64 editions, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 1, Windows XP Service Pack 3
Only the 32-bit Access 2010 Runtime is supported on Windows XP Service Pack 3 (ask me how I know!).
http://www.microsoft.com/en-us/download/details.aspx?id=10910
Package and Deploy Solution Wizard:
Depending on your environment and needs, there are times when the Package and Deploy Solution Wizard comes in handy. It provides a simple way to bundle and deploy Microsoft Access database applications. The wizard guides you through the steps necessary to bundle stand-alone Access applications into Setup packages. You can also include the Access 2010 Runtime and create a shortcut in your package if helpful.
http://office.microsoft.com/en-us/access-help/package-solution-wizard-overview-HA010294985.aspx
Don't forget to add error handling to your VBA code:
You can get quite extensive with error handling but at the least, it's good practice to let the user know something happened and move forward without offering the user the opportunity to "END" or "DEBUG."
At the least include something simple like this:
On Error GoTo Err_Traping
YOUR EVENT CODE
Err_Trapping:
MsgBox err.Description
Exit Sub '(or resume next)
Additional Resources:
http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx
http://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm
http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/6d2da932-05bc-4dac-b3f5-7ce10fb6cabc
http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html
http://msdn.microsoft.com/en-us/library/office/ee358847(v=office.12).aspx
Have I missed anything? Leave a comment, let me know if you disagree with any of my conclusions or add some additional suggestions!
