Improve performance with linked tables in an Access database

Note   You can link a table only in a Microsoft Access database, not a Microsoft Access project.

Although you can use linked tables as if they were regular tables in your Microsoft Access database, it's important to keep in mind that they aren't actually in your Access database. Each time you view data in a linked table, Microsoft Access has to retrieve records from another file. This can take time, especially if the linked table is on a network or in an SQL database.

If you're using a linked table on a network or in an SQL database, follow these guidelines for best results:

• You can greatly enhance performance when opening the main database and opening tables and forms by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and creating and deleting the associated .ldb file.
• View only the data that you need. Don't move up and down the page unnecessarily in the datasheet. Avoid jumping to the last record in a large table. If you want to add new records to a large table, use the Data Entry command on the Records menu to avoid loading existing records into memory.
• Use filters or queries to limit the number of records that you view in a form or datasheet. This way, Microsoft Access can transfer less data over the network.
• In queries that involve linked tables, avoid using functions in query criteria. In particular, avoid using domain aggregate functions, such as Dsum, anywhere in your queries. When you use a domain aggregate function, Microsoft Access retrieves all of the data in the linked table to execute the query.
• If you often add records to a linked table, create a form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Microsoft Access doesn't display any existing records. This property setting saves time because Microsoft Access doesn't have to retrieve the records in the linked table.
• Remember that other users might be trying to use an external table at the same time you are. When a Microsoft Access database is on a network, avoid locking records longer than necessary.