By: Mark Steffen, WSI Senior IT Consultant, with technical insights from Jonathan Estabrook, President of WSI (and Chief Access Officer)

Scaling up a new or older Microsoft Access database that has outgrown the database engine that comes with Microsoft Access can be frustrating. Or you may have a Microsoft Access database that was built from the ground up with Microsoft SQL Server that is not meeting performance expectations. If you have any of these issues, don't lose hope. You are in the right place!

This article provides guidance from the experts of MicrosoftAccessExpert.com, based on 27 years of experience with these two Microsoft technologies and a history of helping hundreds of organizations. It will also focus on what can be done within Microsoft Access and Microsoft SQL Server to optimize database performance.

Outgrowing Microsoft Access' basic database engine does not typically arise until the database grows in complexity, with large numbers of forms, reports, queries, relationships, tables, and ample Microsoft Visual Basic for Applications (VBA) code.

Optimizing a Microsoft Access database by means of SQL Server requires that a developer be seasoned in both Microsoft Access and Microsoft SQL server technology. If you are not that individual, rest assured that there are people that you can assist with this type of enhancement. Winning Solutions, Inc. (WSI), the company behind MicrosoftAccessExpert.com, has those people and is in the business of providing expert Microsoft Access and Microsoft SQL server development services. WSI invites you to reach out to us if you would like assistance implementing the recommendations and would like to discuss the cost of those services.

When considering the optimization of a complex Microsoft Access database, begin by examining the VBA code logic. If the database uses macros, we recommend converting them to VBA. If macros are working fine, they can be left as they are; however, if a macro needs to be modified, this is a good time to convert it to VBA. If the database's logic is sound, or you have just finished fine-tuning it, the next step is to carefully examine the database's indexes. If a database's logic and indexes are sub-optimal, performance will be poor regardless of the resources spent on fast computer hardware.

When making logic changes it is best to make just one or two at a time. After each change, use the database and determine if the changes improved or worsened performance. If the changes made things worse, it is easy to roll back to the previous version of the database and try again (no harm done). Additionally, the knowledge gained during this phase of discovery may help when working on other parts of the database.

The next place to investigate in the optimization journey is any area where users complain about database sluggishness.

It's also important to point out that users should close the Access screen forms when they are not actively in use. Think of the database as a sports car that is running. – It wants to be driven, and it wants to be driven fast, but simply letting your car idle all day in the driveway will be detrimental to future performance. In the same way, letting a database session sit idle for hours unused is detrimental to the overall performance of the application.

This brings up the important point that when a database is not in use, it should also be closed. Just as a proud sports car owner parks their car in the garage when not in use to protect it, unused databases should be closed when not in use to protect the performance of the database. It is to be understood that some users will insist on leaving the database open all day even when they aren't using it because they do not want to repeatedly sign into the database with their username and password. This in-efficiency can be overcome by converting from an Access forms-based authentication process to one based on Microsoft Active Directory. Login based on Microsoft Active Directory allows users to sign into Windows and then their credentials are automatically passed through to the Access database when they launch it. – No re-entry of usernames or passwords, and each individual's unique permissions are applied appropriately.

Microsoft provides multiple "friends" to help get a database running fast. Let's see who they are!

Views are your friend when optimizing an Access database. Whenever two or more database tables are combined, the database should incorporate a view. While there are times when this is not always possible, a view should be used in this circumstance whenever possible.

Microsoft SQL Server Stored Procedures are another good friend to have. Stored Procedures, sometimes called "stored procs," should be used instead of Microsoft Access queries. This is especially true for any queries that involve two or more tables. As with views, there may be circumstances where a stored procedure will not work. Most action queries (insert, update, delete, etc.) can almost always be stored procedures. Any processes where a recordset is used and loops are involved should be converted to a Microsoft SQL Server Stored Procedures.

Display Estimated Execution Plan feature of Microsoft SQL Server is the next friend to have on your optimization journey. When this feature is used on queries and views, it will show missing indexes that are key to performance.

A good rule of thumb to follow is any query/view that takes more than five seconds to complete or start running (in the case of a view with lots of records), in Microsoft SQL Server Management Studio should be reviewed for optimization.

Microsoft's multiple "friends" helping to get a database running fast is why Microsoft Access databases are so popular and are still being created today, even though the technology is 30-plus years old. If an Access database is not performing optimally, it does not mean that it's time to give up on Access, and Access is not giving up on you. It may simply be time for an Access optimization tune-up to get your database back where it needs to be.

If you're a Microsoft Access Developer, we hope we provided all the guidance you need to speed up your database and we wish you luck! WSI fully supports the worldwide Access community and is dedicated to its continued growth.

Of course, it is to be acknowledged that the computer hardware and network a database is run on also impact performance.

If you would like more guidance on computer hardware or networks or if you would like WSI to implement these recommendations for you or would like other expert assistance, please contact WSI today at MicrosoftAccessExpert.com to learn about the cost involved.

Contact WSI to do the programming for you.