Put only the tables on a network server, and keep other database objects on users’ computers.
When Microsoft Access Databases are used in a multi-user environment, the following guidelines show how to optimise the databases performance: When an Access database is on a network, avoid locking records longer than necessary. Remember that multiple users might be trying to use an external table at the same time.This property setting saves time because Access does not have to retrieve all the records in the linked table. When opening the form to enter new data, Access does not display any existing records.
When adding records to a linked table, create a form for adding records that has the DataEntry property set to Yes.When using a domain aggregate function, Access retrieves all of the data in the linked table to execute the query. In particular, avoid using domain aggregate functions, such as Dsum, anywhere in the query. In queries that involve linked tables, avoid using functions in query criteria.This way, Access can transfer less data over the network. Use filters or queries to limit the number of records to be viewed in a form or datasheet.To add new records to a large table, use the Data Entry command on the Records menu to avoid loading existing records into memory.Avoid jumping to the last record in a large table. Do not page up and down unnecessarily in the datasheet. When using a linked table on a network or in an SQL database, follow these guidelines for best results: This can take time, especially if the linked table is on a network or in an SQL database. Each time data is viewed in a linked table, Microsoft Access has to retrieve records from another file. In a multiple-field index, use only as many fields in the index as necessary.Īchieving Optimal Performance with Linked TablesĪlthough linked tables can be used as regular Microsoft Access Database tables, it’s important to keep in mind that they aren’t actually stored in the Microsoft Access database.However, if the application updates data very frequently or if there are disk space constraints, the number of indexes may have to be limited. In most situations, the speed advantages of indexes for data retrieval greatly outweigh these disadvantages. Indexes take up disk space and slow adding, deleting, and updating records.Finding records through the Find dialog box is also much faster when searching an indexed field. Dramatic improvements in the speed of query execution in Microsoft Access Databases can be achieved by indexing fields on both sides of joins, or by creating a relationship between those fields and indexing any field used to set criteria for the query. Create indexes for fields that are sorted, joined, or used in queries.When defining a field, choose the smallest data type or FieldSize that’s appropriate for the data in the field. Space in the database can be saved and join operations can improve by choosing appropriate data types for fields. Choose appropriate data types for fields.If existing tables contain redundant data, the Table Analyzer Wizard can be used to split the tables into related tables to store the data more efficiently.
A well-designed database is a prerequisite for fast data retrieval and updates. To achieve the best performance results, apply the following guidelines for table design in your Microsoft Access Database:
Read on for our great tips on optimising the performance of your Microsoft Access Database. Is your Microsoft Access Database running slowly?