Identifying linked tables

In the “Ways of working with external data” section in Chapter 6, you saw a list of database tables and other types of files that Access links to. Access displays the names of linked tables in the object list and uses a special icon to indicate that the table is linked, not local. An arrow pointing to an icon indicates that the table name represents a link data source. Figure 7.1 shows several linked tables in the list. (The icon indicates that the file is linked. The icon also indicates which type of file is linked to the current Access database. For example, Excel has an X in a box and HTML tables have a globe symbol.)

After you link an external database table to your Access database, you use it as you would any other table. For example, Figure 7.2 shows a query using several linked tables: tblCustomers (a local Access table), tblSales (a linked Access table), tblSaleLineItems (from an Excel file), and Products (from another Excel file). As you can see, there's nothing that distinguishes the fact that the tables are from external sources—Access treats them no differently from any other tables.

This query shows the potential benefit of linking to a variety of data sources and seamlessly displays data from internal and linked tables. Figure 7.3 shows the datasheet returned by this query. Each column in this datasheet comes from a different data source.

Figure 7.3 illustrates an important concept regarding using linked data in Access: Users won't know, nor will they care, where the data resides. All they want is to see the data in a format they expect. Only you, the developer, understand the issues involved in bringing this data to the user interface (UI). Other than the limitations of linked data (explained in the next section), users won't be able to tell the difference between native and linked data.

Limitations of linked data

Although this chapter describes using linked data as if it existed as native Access tables, certain operations can't be performed on linked data. Plus, the prohibited operations depend, to a certain extent, on the type of data linked to Access.

These limitations are relatively easy to understand. Linked data is never “owned” by Access. External files that are linked to Access are managed by their respective applications. For example, an Excel worksheet is managed by Excel. It would be presumptive—and dangerous—for Access to freely modify data in an Excel worksheet. For example, because many Excel operations depend on the relative positions of rows and columns in a worksheet, inserting a row into a worksheet might break calculations and other operations performed by Excel on the data. Deleting a row might distort a named range in the Excel worksheet, causing similar problems. Because there is no practical way for Access to understand all the operations performed on an external data file by its respective owner, Microsoft has chosen to take a conservative route and not allow Access to modify data that might cause problems for the data's owner.

The following list describes the limitations of linked data:

• Access data: There are no limitations to what you can do with the data in linked tables. You can't delete or rename the source table. Nor can you change the fields or data types of the source table.
• Excel data: Existing data in an Excel worksheet can't be changed, nor can rows be deleted or new rows be added to a worksheet. Excel data is essentially treated as read-only by Access.
• Text files: For all practical purposes, data linked to text files is treated as read-only in Access. Although the data can be used in forms and reports, you can't simply and easily update rows in a linked text file, nor can you delete existing rows in a text file. Oddly enough, you can add new rows to a text file; presumably, this is because new rows won't typically break existing operations the way that deleting or changing the contents of an existing row might.
• HTML: HTML data is treated exactly as Excel data. You can't modify, delete, or add rows to an HTML table.
• Outlook contacts: Outlook contacts can be displayed in Access forms and reports, but they can't be added, deleted, or changed.
• ODBC: ODBC is a data access technology that uses a driver between an Access database and an external database file, such as SQL Server or Oracle. Generally speaking, because the linked data source is a database table, you can perform whatever database operations (modifying, deleting, adding) you would with a native Access table, provided you've defined a unique index in Access. (We discuss ODBC database tables in some detail in the “Linking to ODBC data sources” section later in this chapter.)

Linking to other Access database tables

Access easily incorporates data located in the other Access files by linking to those tables. This process makes it easy to share data among Access applications across the network or on the local computer. The information presented in this section applies to virtually any Access data file you linked to from an Access database. Later in this chapter, you'll see short sections explaining the differences between linking to an Access table and linking to each of the other types of data files recognized by Access.

After you link to another Access table, you use it just as you use any table in the open database (with the exception that it can't be used in a relationship to other tables not in the source database). Follow these steps to link to tblSalesPayments in the Chapter07_Link.accdb database from the Chapter07.accdb database file:

1. Open Chapter07.accdb.
2. Select the External Data tab of the Ribbon, and then choose Access as the type of data you want to link. The Get External Data – Access Database dialog box (shown in Figure 7.4) appears.

   

3. Click the Browse button. The File Open dialog box appears.
4. Locate Chapter07_Link.accdb and click Open. The File Open dialog box closes and you're taken back to the Get External Data – Access Database dialog box.
5. Select the option button for linking and click OK in the Get External Data – Access Database dialog box. The Link Tables dialog box enables you to select one or more tables from the selected database (in this case, Chapter07_Link). Figure 7.5 shows the Link Tables dialog box open on Chapter07_Link.accdb.

   

6. Select tblSalesPayments and click OK. Double-clicking the table name won't link the table—you have to highlight it and then click OK.

After you link tblSalesPayments, Access returns to the object list and shows you the newly linked table. Figure 7.6 shows tblSalesPayments linked to the current database. Notice the special icon attached to tblSalesPayments. This icon indicates that this table is linked to an external data source. Hovering over the linked table with the mouse reveals the linked table's data source.

Linking to ODBC data sources

One significant advance with regard to data sharing is the establishment of the Open Database Connectivity (ODBC) standard by Microsoft and other vendors. ODBC is a specification that software vendors use to create drivers for database products. This specification lets your Access application work with data in a standard fashion across many different database platforms. If you write an application conforming to ODBC specifications, then your application will be able to use any other ODBC-compliant back end.

For example, say you create an Access application that uses a SQL Server database back end. The most common way to accomplish this requirement is to use the SQL Server ODBC driver. After developing the application, you find that one of your branch offices would like to use the application as well, but they're using Oracle as a database host. If your application has conformed closely to ODBC syntax, then you should be able to use the same application with Oracle by acquiring an Oracle ODBC driver. Not only are vendors supplying drivers for their own products, but there are now software vendors who only create and supply ODBC drivers.

Linking to non-database data

You can also link to non-database data, such as Excel, HTML, and text files. When you select one of these types of data sources, Access runs a Link Wizard that prompts you through the process.

Linking to HTML files

Linking to data contained in HTML documents is not covered in detail in this book because of the rather severe limitations imposed by Access on this process. For example, Access is unable to retrieve data from an arbitrary HTML file. The data must be presented as an HTML table, in a row-and-column format, and the data has to be relatively clean (absent any unusual data or mix of data, such as text, image, and numeric data combined within a single HTML table).

You're likely to encounter problems if more than one HTML table appears on the page, or if the data is presented in a hierarchical fashion (parent and child data).

The process of linking HTML data is similar to linking to Excel spreadsheets:

1. Select the More drop-down list on the External Data tab of the Ribbon, and then select HTML Document from the list. The Get External Data – HTML Document dialog box appears.
2. Select the Link to the Data Source by Creating a Link Table option, and click Browse. The File Open dialog box appears, enabling you to search for the HTML file you want to link.

From this point on, the process of linking to HTML data is similar to linking to other types of data files, including providing field names and other details of the linked data. Figure 7.9 shows the first screen of the Link HTML Wizard. Click the Advanced button to get to the Link Specification screen (shown in Figure 7.10), where you can provide the field names and other details.

Setting view properties

Although an external table is used like another Access table, you can't change the structure (delete, add, or rearrange fields) of an external table. You can, however, set several properties for the fields in a linked table:

• Format
• Decimal places
• Caption
• Input mask
• Unicode compressions
• IME sequence mode
• Display control

To change these properties, open the linked table in Design view. When you open a linked table in Design view, Access warns you that the design can't be modified. Figure 7.13 shows a warning when the Products table is opened in Design view. Despite that warning, the above properties can be changed.

Setting relationships

Linking to external Access tables maintains the relationships that might exist between the external tables. Therefore, when linking to a back-end database, the relationships you've established in the back end, as well as any validation and default values, are recognized and honored by the front-end database. This is a good thing, because it means that the rules you've defined will be enforced regardless of how many front ends are created to use the tables.

Optimizing linked tables

When working with linked tables, Access has to retrieve records from another file. This process takes time, especially when the table resides on a network or in a SQL database. When working with external data, optimize performance by observing these basic rules:

• Avoid using functions in query criteria. This is especially true for aggregate functions, such as DTotal or DCount, which retrieve all records from the linked table before performing the query operation.
• Limit the number of external records to view. Create a query using criteria that limit the number of records from an external table. This query can then be used by other queries, forms, or reports.
• Avoid excessive movement in datasheets. View only the data you need to in a datasheet. Avoid paging up and down and jumping to the first or last record in very large tables. (The exception is when you're adding records to the external table.)
• If you add records to external linked tables, create a form to add records and set the DataEntry property to True. This makes the form an entry form that starts with a blank record every time it's executed. Data entry forms are not pre-populated with data from the bound table. Using a dedicated data entry form is much more efficient than building a normal form, populating it with data from the linked source, and then moving to the end of the linked data just to add a new record.

Deleting a linked table reference

Deleting a linked table from your database is a simple matter of performing three steps:

1. In the Navigation Pane, select the linked table you want to delete.
2. Press the Delete key, or right-click the linked table and select Delete from the shortcut menu.
3. Click OK in the Access dialog box to delete the file.

Viewing or changing information for linked tables

Use the Linked Table Manager to update the links when you move, rename, or modify tables, indexes, or relationships associated with linked tables. Otherwise, Access won't be able to find the data file referenced by the link.

1. Select the External Data tab of the Ribbon and click the Linked Table Manager button. The Linked Table Manager (shown in Figure 7.14) appears, enabling you to locate the data files associated with the linked tables in the database.

   

2. Click the check box next to a linked table and click OK.
3. Find the missing file and relink to Access. If all the files are already linked correctly, clicking OK makes Access verify all the linkages associated with all the selected tables.
4. If you know all the linked data sources have been moved, select the Always Prompt for a New Location check box, and then click OK. Access then prompts you for the new location, and links all the tables as a batch process. You'll find this operation much faster than linking one or two tables at a time.

Refreshing linked tables

The data in linked tables is kept in sync with the source data automatically by Access. No user intervention is required to have up-to-date data. When a linked table is open in Datasheet view or otherwise being used, Access attempts to restrict access to the source data depending on what kind of data it is. For instance, Access locks linked text files that are in use so that you can't open them in a text editor.

To demonstrate how linked data is synchronized automatically, create a linked table to a text file and edit that text file by following these steps:

1. Click Text File from the Import & Link group on the External Data tab of the Ribbon.
2. Create a linked text file from the file named ContactsFixed.txt, a fixed-length text file. The file is well formatted, so Access will guess correctly about where the fields begin. Don't worry about field names for this exercise.
3. Open the linked table ContactsFixed. Note that it contains 12 records. If you attempt to open ContactFixed.txt in a text editor, Windows will tell you that it's being used by another process and won't allow you to open it.
4. Close the ContactsFixed table.
5. Add a new row to the text file using your favorite plain text editor, such as Notepad. Don't use Word for this process, because you'll risk saving it in a format that's not plain text. Figure 7.15 shows the new ContactsFixed.txt file.

   

6. Save the file and close the text editor.
7. In Access, open the ContactsFixed linked table. The linked table now contains a 13th row.

The benefits of splitting a database

There is at least one extremely good reason why you should consider splitting your Access databases. Although you can place a single copy of an ACCDB or MDB file onto a shared computer on the network, the performance degradation from such a design is considerable.

Using an Access database stored on a remote computer involves much more than simply moving data from the remote computer to the local machine. All the form, menu, and Ribbon definitions must be transported to the local computer so that Windows can “construct” the UI on the local computer's monitor. The Windows installation on the local computer must intercept and transmit any keyboard and mouse events to the remote computer so that the proper code will run in response to these events. Finally, the single copy of Access on the remote computer must fulfill all data requests, no matter how trivial or demanding. The impact of all these actions is compounded by increasing the number of users working with the same remotely installed copy of the database.

Fortunately, most of these issues disappear when the database application is split into front-end and back-end components. The local Windows installation handles the UI from information stored in the front-end database. All code is run on the user's desktop computer, rather than on the remote machine. Also, the locally installed copy of Access is able to handle all local data requirements, while only those requests for remote data are passed on to the back-end database.

Before getting into the details of splitting a database, let's consider some of the problems associated with single-file databases. To begin with, unlike some other development systems, all the objects in an Access database application are stored in a single file, the familiar ACCDB or MDB you work with every day. Many other database systems like FoxPro for Windows maintain a number of different files for each application, usually one file per object (form, table, and so on). Although having to deal with multiple files complicates database development and maintenance somewhat, updating a single form or query involves nothing more than replacing the related file with the updated form or query file.

Updating an Access database object is somewhat more complicated. As you've probably discovered, replacing a form or query in an Access database used by a large number of users can be quite a problem. Replacing a form or other database object often requires hours of work importing the object into each user's copy of the database.

A second consideration is the network traffic inherent in single-file Access databases. Figure 7.16 shows an example of the problem. This figure illustrates a common method of sharing an Access database. The computer in the upper-left corner of the figure is the file server and holds the Access database file. Assume for a moment that the entire database is contained within a single ACCDB on the file server, and the database has been enabled for shared data access. Each workstation in Figure 7.16 has a full copy of Access (or the Access RunTime) installed.

Now, what happens when the user on Workstation C opens the database? The Access installation on that machine must locate the ACCDB on the file server, open that file, and start up the application. This means that any splash forms, queries, and other startup activities must take place across the network before the user is able to work with the database. Any time a form is opened or a query is run, the information necessary to fulfill the query must travel across the network, slowing the operation. (In Figure 7.16, the network load is indicated by a thick dashed line.)

The situation shown in Figure 7.16 is made even worse when more than one user is using the same database. In this case, the network traffic is increased by the queries, opening of forms, and other operations performed by each additional user's copy of Access. Imagine the dashed line getting thicker with each operation across the network.

The split-database model is illustrated in Figure 7.17. Notice that the back-end database resides on the server while individual copies of the front-end database are placed on each workstation. Each front-end database contains links to the tables stored in the back-end ACCDB file. The front-end databases also contain the forms, reports, queries, and other user-interface components of the application.

The network traffic is reduced in Figure 7.17 because only linking information and data returned by queries is moved across the network. A user working with the database application uses the forms, queries, reports, macros, and code stored in the local front-end ACCDB file. Because the front end is accessed by a single user, response time is much improved because the local copy of Access is able to instantly open the database and begin the startup operations. Only when actually running queries does the network traffic increase.

The second major benefit of the split database design is that updating the forms, reports, and other application components requires nothing more than replacing the front-end database on each user's computer and reestablishing the links to the table in the back-end database. In fact, the design in Figure 7.17 supports the notion of customized front ends, depending on the requirements of the user sitting at each workstation. For example, a manager sitting at Workstation A might need access to personnel information that is not available to the people sitting at workstations B and C. In this case, the front-end database on Workstation A includes the forms, queries, and other database objects necessary to view the personnel information.

Knowing where to put which objects

The local ACCDB contains all the UI objects, including forms, reports, queries, macros, and modules. Keeping the UI components on the local machine dramatically improves performance. You don't need to move forms, queries, or reports across the network—these objects are much more easily manipulated on the local machine than when accessed across the network.

All shared tables should be placed in the back-end database kept on the server, along with all relationships between those tables. The server database is opened in Shared mode, making all its objects accessible to multiple users. The tables in the server database are linked to the front-end ACCDB on each user's Desktop. (There is no problem with simultaneously linking the same table to multiple databases.)

Obviously, with more than one person using the data within a table, the possibility exists that the same record will be edited by multiple users. The Access database engine handles this problem by locking a record as it's edited by a user. A lock contention occurs when more than one user tries to update the same record. Only one user will have “live” access to the record—all other users will either be locked or have their changes held up until the record holder is done making changes.

Using the Database Splitter add-in

The Database Splitter helps you split an application into front-end and back-end databases. This wizard enables you to build and test your database to your heart's content, and then lightens the burden of preparing the application for multiuser access.

As an experiment, let's take a look at splitting the Northwind Traders database into front-end and back-end ACCDB files. You start the Database Splitter by selecting the Database Tools tab of the Ribbon and then clicking the Access Database button in the Move Data group. The opening wizard screen (shown in Figure 7.18) explains the actions of the Database Splitter and suggests that you make a backup of the database before proceeding.

The only other information that the Database Splitter requires is where you want to put the back-end database. Figure 7.19 shows the familiar Explorer-style Create Back-end Database dialog box that lets you specify the location of the back-end ACCDB file. By default, the back-end database has the same name as the original database with a _be suffix added to the name (for example, MyDB_be.accdb).

When you click the Split button (refer to Figure 7.19), the Database Splitter creates the back-end database, exports all tables to it, deletes the tables in the local database, and creates links to the back-end tables. In other words, the Database Splitter performs precisely the same steps you'd have to perform manually if the Database Splitter weren't available.

Figure 7.20 shows the Access Database Explorer after splitting the Northwind Traders database. The back-end database contains only the tables exported from Northwind.accdb. Notice that the icons associated with all the tables in Northwind.accdb have been changed, indicating that they're now pointing to copies in the back-end database. You'll have to import any local tables from the back-end database before distributing the front end to the users.