Whenever possible, normalizing the tables is highly recommended. Normalizing tables will reduce several tables into a few tables. Refer to the features section on how to normalize data.
Use dropdown lists for text fields that should limit the user to a predefined list of values. An example is when entering an address use a dropdown list for the ‘State’.
Try to avoid using memo fields if possible. Memo fields can make Access databases become unstable in certain situations.
Every table should always have an index. Indexes enable the database to quickly access data when it is normalized. If a table does not have an index, it will take the database longer to pull the information. Make sure to set an index with no duplicates.
When creating an index try to use one or more data fields that are not of auto number type. This will save time when trying to manually work in data tables.
Another consideration is to not store calculated values in the table. For example if the application is a billing system and you are working with an invoice table. Do not store the invoice total in the table. This can be easily calculated by adding up the total of all the items in the invoice.
Go through and make sure text fields that are more than 3 characters in size have the Unicode Compression set to ‘Yes’. This will help the Access database use less hard drive space.
Unstructured data such as video
