MS-Access / Getting Started

---

Linking Files

You can link tables from other Access databases-whether the other databases are local or on a network-and work with the data as if these tables were defined in your current Access database. If you want to work with data stored in another database format supported by Access (dBASE, or any SQL database that supports ODBC), you can link the data instead of importing it.

Note: Although you can import queries, forms, reports, macros, and modules from another Access database file, you cannot link these types of objects. Any object that Access needs to run (rather than simply be a container for data) must be in your local database.

In most cases, you can read data, insert new records, delete records, or change data just as if the linked file were an Access table in your database. You can also link text and spreadsheet format data so that you can process it with queries, forms, and reports in your Access database. You can only read the data in linked text and spreadsheet files.

This ability to link data is especially important when you need to access data on a host computer or share data from your application with many other users.

Note: Access 2010 supports linking to dBASE, and it allows full update if it can find the associated index files. If you need to work with later versions, you must install the Borland Database Engine (BDE).

Security Considerations

If you attempt to link a file or a table from a database system that is protected, Access 2010 asks you for a password. If the security information you supply is correct and Access successfully links the secured data, Access can store the security information with the linked table entry so that you do not have to enter this information each time you or your application opens the table. Access stores this information in the hidden Connect property of a linked table, so a knowledgeable person might be able to retrieve it by writing code to examine this property. Therefore, if you have linked sensitive information to your Access database and have supplied security information, you should consider encrypting your database.

If you are linking your database to SQL Server tables and are using Windows domain security, you can set options in SQL Server to accept the Windows domain user ID if the user logs on correctly to the network. Therefore, you won't need to store security information with the link. If your server contains particularly sensitive information, you can disable this option to guard against unauthorized access from logged on but unattended network workstations.

Performance Considerations

Access 2010 always performs best when working with its own files on your local computer. If you link tables or files from other databases on other computers, you might notice slower performance. In particular, you can expect slower performance if you connect over a network to a table or a file in another database, even if the remote table is an Access table. You won't see any performance difference if you link to Access tables in another .accdb file on your local computer.

When sharing data over a network, you should consider how you and other people can use the data in a way that maximizes performance. For example, instead of working directly with the tables, you should work with queries on the shared data whenever possible to limit the amount of data you need at any one time. When inserting new data in a shared table, you should use an Access form that is set only for data entry so that you don't have to access the entire table to add new data.

You can view and set options for multiple users sharing data by clicking the File tab on the Backstage view, clicking Options, and then clicking the Client Settings category in the Access Options dialog box. The original settings for these options are often appropriate when you share data over a network, so it's a good idea to consult your system administrator before making changes.

One very important consideration is record locking. When Access 2010 needs to update data in a shared file, it must lock the data to ensure that no other computer is trying to write the same data at the same time. You should set options so that records are not locked if you are simply browsing through data. Even if your application frequently updates and inserts data, you should leave Default Record Locking set to No Locks. With this setting, Access 2010 locks individual records only for the short period of time that it is writing the row, so the chance of receiving an update error while two users are trying to update the same row at the exact same time is very small.

If you want to ensure that no one else can change a record that you have begun to update, you should set Default Record Locking to Edited Record. Note, however, that no other user will be able to edit a record that another has begun to change. If a user begins to type a change in a record and then goes to lunch, no one else will be able to change that record from another computer until that user either saves the row or clears the edit.

We never set either All Records or Edited Record as the default. Either one can cause extra overhead while updating data and can lock out other users unnecessarily. In the rare case that an update conflict occurs with No Locks, Access gives the second user the opportunity to refresh the data and reenter the blocked update. Also, you can set record locking individually in forms and reports.

You can set options to limit the number of times Access 2010 will retry an update to a locked record and how long it will wait between retries. You can also control how often Access reviews updates made by other users to shared data by setting the refresh interval. If this setting is very low, Access will waste time performing this task repeatedly.

Access 97 (version 8) and earlier locked an entire 2-KB page each time you updated, inserted, or deleted rows. This meant that only one user could update any of the rows stored physically within the page. The page size in Access 2000 increased to 4 KB, but Access 2000 (version 9) and later also support record-level locking that eliminates locking collisions when two users attempt to update different rows stored on the same data storage page. Unless you are designing an application that frequently needs to update hundreds of rows at a time (for example, with action queries), you should leave the Open Databases By Using Record-Level Locking check box selected.