Timing is very important in everything we do in life, in this post you will find out tricks to easily optimize your speed when working with MS Access database, this tips will also help you manage the time while creating access database.
- Each table should have primary keys: when creating access database in MS access make sure, each and every table in your access database should have primary keys. This allows the system to quickly locate and connect records with other tables, which are normally known as a secondary index on a connecting table’s field along-side the primary key field.
- Eliminating Subdatasheet’s: when working with MS access database, it creates unnecessary Subdatasheet’s between the related tables, this has a huge performance hit if the tables are open, it’s advisable you set this property none when working with MS access in other to save more time and optimize your speed.
- Don’t apply the index to the field with identical data: if you have a number fields in your MS access database that contains two to three values, applying index would may not be helpful, Instead to check the number of entries in an index use the access distinct count property in MS access.
- Use shortcuts often: This is important when working with MS access or when working with other Microsoft Office application, you should be able to work with shortcuts in order save a lot of time.
- Keep fields (columns) to a minimum in a table: It’s a misconception to think the number of records in a table can slow down your MS Access database when in fact it’s the number of fields that is more memory sensitive due to the allocation of memory for each field (or column).
- Close unused forms:Make sure to close every unused form when working with an access database because forms tend to consume more memory and if it’s not in use it should be closed in other to free up memory, releasing memory for some other part of MS access application to use.
- Use light weighted forms: light weighted form should replace the visual basic code (VBA) in forms. Instead, consider setting the form’s Has Module property to false, and by doing so it turns a form into a lightweight form.
- Create a data entry form: if the nature of access database requires you to constantly add new records to a linked table. Then you need to consider setting the form data entry property to yes, this will prevent MS access from retrieving all existing records in a linked table when only needing to add new records.
- Reduce your server traffics: traffics tend to slow down speed and reduce performance so to optimize your speed you need to reduce the amount of traffic returning to the server.
- Use Dynasts for the large record: when working with MS access database and you need to retrieve a large set of data, make sure you use a dynast instead of using snapshot.
MS access is a great database tool and has great built-in functions to help you create a database, explore more.