Correctly normalize your data to remove redundant data. You can use the Table Analyzer Wizard to help you split your tables into related tables. A better approach is to become familiar with relational theory and the concepts of data normalization.
Search Microsoft Access Help for: normalizing tables or check out the following articles:
Choose the best data types for your fields. By choosing the optimal data type, you can decrease both the disk space used to store data, and the time it takes Access to retrieve, manipulate, and write data. The general guideline is to choose the smallest data type possible to store a particular type of data.
Search Microsoft Access Help for: data types, using efficiently or check out the following articles:
If you are going to search on a field, or use it in a join, index the field. Indexes offer performance gains on an order of magnitude.
Search Microsoft Access Help for: indexes, optimizing performance
Just as it is important to add indexes to fields that need it, it is important to avoid indexing fields that don't need it. Every index adds to the time it takes the database engine to update, delete and add records.
Search Microsoft Access Help for: indexes, optimizing performance
Don't apply indexes to fields that contain much the same data. For example, indexing a Yes/No field is almost always a performance degrading operation. Similarly, if you have a number field that only contains two or three values, and index wouldn't be a good idea. To check the number of unique entries in an index, use the Access DistinctCount property. Compare this value to the number of records in the table and you can quickly see if the index is doing you any good.
Search Microsoft Access Help for: indexes, optimizing performance and DistinctCount
When creating a multi-field index, index only as many fields as are absolutely necessary.
Search Microsoft Access Help for: indexes, optimizing performance
Every table in your database should have a primary key. This allows your database application to quickly access specific records. Additionally, you cannot create secondary indexes on a table's fields unless that table as a Primary Key.
Relationships allow joins between tables to work faster. Also, relationships that implement referential integrity allow cascading of updates and deletions without writing VBA code.
Search Microsoft Access Help for: Relationships