There are particular items that are the most important for MS Access database design. There are also some items that cause errors, slow performance, data corruption and user difficulties. In order to avoid any problems in the database work, you should follow these guidelines. And if you are new to application, this will help you with the learning curve.

In order to make your form performance or the loading of the drop down list better, you should use stored queries as your record sources. They were already optimized by Access. You should also use validation rules for preventing errors in data entry and restrict the type of information entered in the field. You can let the user know what is needed by using a message box. It is easy to encode the type of validation for each field using the field tag in the right way. To increase the speed of performance, index the fields you need for criteria selection and fields in queries that join the tables.

In order to have control over what logic to apply, always put code error handling to your routines. For example, an MDE file will stop working without informing you about the error.

Don’t allow users the original front-end program file and let them use only the copy of the file, which contains everything but the data. This way you can prevent the risk of corruption. It is also important to split your database into front-end and back-end. For quicker reads and writes, place the back-end data on the root of the network share. And compact your back-end data regularly to check if there is any data that is corrupted. You will also be able to reduce the file size this way.

When you compact, make sure that your version of Access is up to date and there is no reissuing of the auto numbers that were previously issued. You should also make sure you have the back-up copy of the original MDB. Avoid using Memo data type fields, as they have different classification and are more vulnerable to corruption. Use the non-data entry form or a main menu form that is always kept open and is linked to a small table. It will prevent a slow network connection.

In order to link tables to dependent children tables, use auto number field. They offer better indexing and are hard for users to modify. The same name is supposed to be used in parent and child table. Avoid using record sets in database with multiple users unless the updates don’t conflict with the performance of other users and the record set is read only snapshot. The solution would be to wrap the updates in a transaction which will notify of conflicts.

These are only some of the important points that can make the work of your database more effective.

Everything about microsoft access database: great training info, all sorts of business products (that can be tailored for the specifics of your business), special microsoft access database blog with tips and tricks about MA databases.

All this and more from a trustworthy UK based company which specializes in microsoft access database and is helping clients round the globe.