Chapter 16: Working with External Data
So far, you’ve worked with data in Access tables found within the current database. In this chapter, you explore the use of data from other types of files. You learn to work with data from database, spreadsheet, HTML, and text-based files. After we describe the general relationship between Access and external data, we explain the major methods of working with external data: linking and importing/exporting.
This chapter uses the Chapter16.accdb database as well as several other files that you will use for linking. If you have not already copied these files onto your machine from the CD, you’ll need to do so now.
The data linked or imported into Access applications comes in a bewildering variety of formats. There is no practical way to document every possible type of import or linking operation in a single chapter. Therefore, this chapter discusses the essential steps required to import or link to external data, and gives a few examples demonstrating how these processes are performed in Access 2007, instead of filling page after page with examples that may or may not be relevant to your work.
As you’ll soon see, knowledge of the external data format is critical to a successful import or linking operation. You must have some notion of the external data format before you can successfully import data into your Access application or incorporate the data into your Access application through linking. This chapter points out many of the issues involved if you choose to import or link to external data; it’s intended to be a guide as you perform these operations in your Access applications.
Access and External Data
Exchanging information between Access and another program is an essential capability in today’s database world. Information is usually stored in a wide variety of application programs and data formats. Access, like many other products, has its own native file format, designed to support referential integrity and provide support for rich data types, such as OLE objects. Most of the time, this format is sufficient; occasionally, however, you need to move data from one Access database file to another, or even to or from a different software program’s format.
Types of external data
Access has the capability to use and exchange data among a wide range of applications. For example, you may need to get data from other database files (such as FoxPro, dBASE, or Paradox files) or obtain information from a SQL Server, Oracle, or a text file. Access can move data among several categories of applications:
• Other Windows applications
• Macintosh applications (FoxBASE, FoxPro, Excel)
• Spreadsheets
• PC database-management systems
• Server-based database systems (SQL Server)
• Text and mainframe files
Methods of working with external data
Often, you need to move data from another application or file into your Access database, or vice versa. You may need to obtain information you already have in an external spreadsheet file. You can re-enter all the information by hand—or have it automatically imported into your database. Access has tools that enable you to move data from a database table to another table or file. It could be a table in Access, FoxPro, or Paradox; it could be an Excel spreadsheet file. In fact, Access can exchange data with more than 15 different file types:
• Access database objects (all types, all versions)
• dBASE
• Microsoft FoxPro
• Paradox
• Text files (ANSI and ASCII; DOS or OS/2; delimited and fixed-length)
• Lotus 1-2-3
• Microsoft Excel
• ODBC databases (Microsoft SQL Server, Sybase Server, Oracle Server, and other ODBC-compliant databases)
• HTML tables, lists, documents
• XML documents
• Microsoft Outlook and Outlook Express
• Microsoft Exchange documents
• Microsoft IIS
• Microsoft SharePoint
• Microsoft Active Server Pages
• Microsoft Word Merge documents
• Rich Text Format documents
Access works with these external data sources in several ways: linking, importing, and exporting. Table 16-1 describes these methods.
As Table 16-1 shows, you can work with data from other sources in two ways: linking or importing. Both methods enable you to work with the external data. There is a distinct difference between the two methods:
• Linking uses the data in its current file format (such as Excel or FoxPro). The link to data remains in its original file. The file containing the link data should not be moved, deleted, or renamed. Otherwise, Access will not be able to locate the data the next time it’s needed.
• Importing makes a copy of the external data and brings the copy into the Access table. The imported data is converted to the appropriate Access data type and is managed by Access from that point on.
Each method has clear advantages and disadvantages, covered in the following sections.
When to link to external data
Linking in Access enables you to work with the data in another application’s format—thus, sharing the file with the existing application. If you leave data in another database format, Access actually changes the data while the original application is still using it. This capability is useful when you want to work with data in Access that other programs also need to work with. Another example is when you use Access as a front end for a SQL Server database—you can link to a SQL Server table and directly update the data, without having to batch-upload it to SQL Server.
If you plan to use a table from another Microsoft Access database, it’s a good idea to simply link to it rather than import it. If another application continues to update and work with data, it’s best to link to it.
You can link to the following types of data in Access: other Access tables (.accdb, .accde, .mdb, .mda, .mde), Excel spreadsheets, Exchange documents, Outlook documents, FoxPro, Paradox or dBASE, text files, HTML documents, SharePoint Team Services, and ODBC databases.
Access 2007 has the capability to link to HTML tables and text tables for read-only access. You can use and look at tables in HTML or text format; however, the tables cannot be updated and records cannot be added to them using Access. Also, if you are working with Paradox files and they don’t have a primary key field defined, you will only be able to read the data—not change it.
The biggest disadvantage of working with linked tables is that you lose the capability to enforce referential integrity between tables (unless you’re linked to an Access database).
When to import external data
Importing data enables you to bring an external table or data source into a new Access table. By doing this, Access automatically converts data from the external format and copies it into Access. You can even import data objects into a different Access database or Access project than the one that is currently open. If you know that you’ll use your data in Access only, you should import it. Generally, Access works faster with its own tables.
Because importing makes another copy of the data, you may want to erase the old file after you import the copy into Access. Sometimes, however, you won’t want to erase it. For example, the data may be sales figures from an Excel spreadsheet still in use. In cases such as this, simply maintain the duplicate data and accept that storing it will require more space.
One of the principal reasons to import data is to customize it to meet your needs. After a table has been imported, you can work with the new table as if you’d built it in the current database.
With linked tables, on the other hand, you’re greatly limited in the changes you can make. For example, you cannot specify a primary key or assign a data-entry rule, which means that you can’t enforce integrity against the linked table.
Access opens only one database at a time. Therefore, you can’t work directly with a table in a different database. If you need to work with tables or other Access objects (such as forms and queries) in another Access database, simply import the object from the other database into your current database.
Data is frequently imported into an Access database from an obsolete system being replaced by a new Access application. When the import process is complete, the obsolete application can be removed from the user’s computer. The data formerly managed by the obsolete system, such as an old FoxPro or dBASE application, is preserved in the Access database.
Data in unsupported programs
Although uncommon, there may be times when you need to work with data from a program that is not stored in the supported external database or file format. In cases such as this, the programs usually can export or convert their data in one of the formats recognized by Access. To use the data in these programs, export it into a format recognized by Access and then import it into Access. For example, many applications can export to the dBASE file format. If the dBASE format is not available, most programs, even those on different operating systems, can export data to delimited or fixed-width text files, which you can then import into Access.
Automating import operations
If you will be importing data from the same source frequently, you can automate the process with a macro or a VBA procedure. This can be very helpful for those times when you have to import data from an external source on a regular schedule or you have complex transformations that must be applied to the imported data.
Linking External Data
As the database market continues to grow, the need to work with information from many different sources will escalate. If you have information captured in a SQL Server database or an old Paradox table, you don’t want to reenter the information from these sources into Access. Ideally, you want to open an Access table containing the data and use the information in its native format, without having to copy it or write a translation program to access it. For many companies today, this capability of accessing information from one database format while working in another is often an essential starting point for many business projects.
Copying or translating data from one application format to another is both time-consuming and costly. The time it takes can mean the difference between success and failure. Therefore, you want an intermediary between the different data sources in your environment.
Access can directly simultaneously link to multiple tables contained within other database systems. After an external file is linked, Access builds and stores a link to the table. Access easily links to other Access database tables as well as to non-Access database tables such as dBASE, FoxPro, and Paradox. A common practice is to split an Access database into separate databases, for easier use in a multiuser or client-server environment.
Linking to external database tables
In the “Methods of working with external data” section, earlier in this chapter, you saw a list of database tables and other types of files that Access links to. Access displays the names of link tables in the object list but uses a special icon to indicate that the table is linked and not contained within the current Access database. An arrow pointing to an icon indicates that the table name represents a link data source. Figure 16-1 shows several linked tables in the list, which are all external tables. Notice that all the linked tables have an icon containing is an arrow. (The icon clues you in to the type of file that is linked.)
Figure 16-1
Linked tables in an Access database. Notice that each linked table has an icon indicating its status as a linked table.
The icon indicates which type of file is linked to the current Access database. For instance, Excel has an X symbol in a box, Paradox has a Px symbol, and dBASE tables have a dB symbol.
After you link a table to your Access database, you use it as you would any other table. For example, Figure 16-2 shows a query using several linked tables: Contacts (from a dBase table), Sales (from a Paradox table), SalesLineItems (from a comma-delimited text file), and Products (from an Excel file).
This query shows the potential benefit of linking to a variety of data sources and seamlessly displays data from internal and linked tables. Figure 16-3 shows the datasheet returned by this query. Each column in this datasheet comes from a different linked data source.
Figure 16-2
A query designed using externally linked tables
Figure 16-3
The datasheet view of externally linked data
In Figure 16-3, the column heading names come from the field names in the underlying external tables. For instance, the first column (BuyerName) is a combination of the FNAME and LNAME fields from the dBASE table, while the invoice number is from Paradox, and the Description field comes from Excel.
Figure 16-3 illustrates an important concept regarding using linked data in Access. Users will not know, nor will they care, where the data resides. All they want is to be able to see the data in a format they want and expect. Only you, the developer, understand the issues involved in bringing this data to the user interface. Other than the limitations of linked data (explained in the “Limitations of linked data” section), users will not be able to tell the difference between native and linked data.
After you link an external table to an Access database, you should not move the table to another drive or directory. Access does not bring the external data file into the .accdb file; it maintains the link via the filename and the file’s path. If you move the external table, you have to update the link using the Linked Table Manager, explained in the “Viewing or changing information for linked tables” section, later in this chapter.
Limitations of linked data
Although this chapter describes using linked data as if it existed as native Access tables, certain operations cannot be performed on linked data. Furthermore, 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 instance, an Excel worksheet is managed by Microsoft Excel. It would be presumptive—and dangerous—for Access to freely modify data in an Excel worksheet. As an example, because many Excel operations depend on the relative positions of rows and columns in a worksheet, inserting a row into a worksheet may break calculations and other operations performed by Excel on the data. Deleting a row may distort a named range in the Excel worksheet, causing similar problems. Because there is no practical way for Access to understand all of the operations performed on an external data file by its respective owner, Microsoft has chosen to take a very conservative route and not allow Access to modify data that may cause problems for the data’s owner.
The following list describes the limitations of linked data:
• Excel data: Existing data in an Excel worksheet cannot be changed, nor can rows be deleted or new rows added to a worksheet. For all intents and purposes, Excel data is treated in a read-only fashion 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 link 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 will not typically break existing operations the way that deleting or changing the contents of a row may.
• HTML: HTML data is treated exactly as Excel data. You cannot modify, delete, or add rows to an HTML table.
• Paradox and dBASE: Because these are database files, you can pretty much perform the same data operations on Paradox and dBASE tables as you can on native access tables. This general statement applies only if a primary key is provided for each Paradox or dBASE table.
• ODBC: Briefly, ODBC is a data access technology that utilizes a driver between an Access database and an external database file, such as Microsoft SQL Server or Oracle. Again, generally speaking, because the linked data source is a database table, you can perform whatever database operations (modify, delete, add) as you would with a native Access table.
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 data file you linked to from an Access database. Rather than include examples of linking to every type of data file, this section explains the principles involved when linking to any type of data file. 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.
A very common practice among Access developers is splitting an Access database into two pieces. One piece contains the forms, reports, and other user interface components of an application, while the second piece contains the tables, queries, and other data elements of the application. There are many advantages to splitting Access databases, including certain performance benefits as well as easier maintenance. You can read about splitting Access databases in the “Splitting an Access database” section, later in this chapter. The process of linking to external Access tables described in this section is an essential part of a split database paradigm. The steps described in this section are frequently performed win managing split databases.
After you link to another Access table, you use it just as you use another table in the open database. Follow these steps to link to tblSalesPayments in the Chapter16_Link.accdb database from the Chapter16.accdb database file:
1. Open the Chapter16.accdb database.
2. Select the External Data ribbon, and then choose the type of data you want to access.
Access opens the Get External Data dialog box, shown in Figure 16-4.
Figure 16-4
Use the Get External Data dialog box to select the type of operation you want to perform on the external data sources.
3. Use the Browse button to open the Windows File Open dialog box, and locate the .accdb file you want to link to.
4. Find and select the Chapter16_Link file in the File Open dialog box and click the Open button.
The File Open dialog box closes and you’re taken back to the Get External Data dialog box.
5. Click the OK button in the Get External Data dialog box.
The Link Tables dialog box enables you to select one or more tables from the selected database (in this case, Chapter16_Link). Figure 16-5 shows the Link Tables dialog box open on Chapter16_Link.accdb.
6. Select tblSalesPayments and click OK.
Double-clicking the table name will not select the table—you must highlight it and then click OK.
Figure 16-5
Use the Link Tables dialog box to select the Access table(s) for linking.
After you link tblSalesPayments from the Chapter16_Link database, Access returns to the object list and shows you the newly linked table. Figure 16-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.
Figure 16-6
The Navigation Pane with tblSalesPayments added. Notice the icon indicating that this is a linked table.
You can link more than one table at a time by selecting multiple tables before you click the OK button. Clicking the Select All button (naturally!) selects all the tables.
Linking to ODBC data sources
One significant advance with regard to data sharing has been the creation and support of Open Database Connectivity (ODBC) 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 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 Microsoft 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 purchasing 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 dBASE databases (tables)
Unlike Access, dBASE (and FoxPro and other xBase systems) store each table as a separate file with a .dbf extension. Each .dbf file may be accompanied by an .ndx or .mdx file containing the indexes associated with the dBASE table.
When you link to a dBASE table, Access may ask you if you want to link to the index file associated with the dBASE table. In almost every case, you’ll want to include the index file in the linking operation. Otherwise, the dBASE data will be read-only and not updatable.
One other significant difference between Access and dBASE is that the links of table and field names are much shorter in dBASE than in Access, and are almost always expressed in all uppercase characters.
Linking to dBASE or other xBase data files is much like linking to an external Access table. The main difference is that you select dBASE (or FoxPro) from the Files of Type drop-down list in the File Open dialog box, and, because each xBase file is a table, you don’t have to specify which table to link. Otherwise, the processes are virtually identical.
This book’s CD includes a dBASE IV file named CONTACTS.dbf containing a copy of the Contacts table from the Access Auto Auctions application. You may want to use this file to practice linking the base tables.
Linking to Paradox tables
Linking to Paradox tables is much like linking to dBASE files. Each Paradox table is kept in a separate file with a .db extension. Each table’s primary key and indexes is kept in a file with a .px or .mb extension. Otherwise, the Paradox linking operation parallels linking to dBASE files. Be sure to include the index file (if it exists) when linking to a Paradox .db file.
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 Excel
The main issues to keep in mind when linking to Excel data are:
• An Excel .xls workbook file may contain multiple worksheets. You must choose which worksheet within a workbook file to link.
• You may link to individual named ranges within an Excel worksheet.
• Excel columns may contain virtually any type of data.
The last bullet above is fairly important. Just because you have successfully linked to an Excel worksheet does not mean that your application will be able to use all of the data contained in the Excel worksheet. Because Excel does not limit the types of data contained in a worksheet, your application may encounter multiple types of data within a single column of a linked Excel worksheet. This means you may have to add code or provide other strategies for working around the varying types of data contained in an Excel worksheet.
This book’s CD contains an Excel worksheet created by exporting the Products table from the Access Auto Auctions application. Use this file to practice importing Excel data, keeping in mind that, in practice, the data you’re likely to encounter in Excel worksheets is far more complex and less orderly than the data contained in the Products.xls file.
By linking to an Excel table, you can update its records from within Access or any other application that updates Excel spreadsheets.
Follow these steps to link to the Excel Products spreadsheet:
1. In the Chapter16 database, select the Excel button on the External Data ribbon.
2. In the Get External Data dialog box, select Link to the Data Source by Creating a Linked Table, then click the Browse button.
The same Get External Data dialog box (see Figure 16-7) is used for both import and link operations. Therefore, be sure the correct operation is elected before continuing.
Importing data into Access is discussed in Chapter 17.
Figure 16-7
The first screen of the Link Spreadsheet Wizard
3. Use the File Open dialog box to locate and open the Excel workbook file.
You’ll be returned to the Link Spreadsheet Wizard (see Figure 16-8).
Figure 16-8
The main Link Spreadsheet Wizard screen
Notice that the Link Spreadsheet Wizard dialog contains options for selecting either worksheets or named ranges within the workbook file. In this example, there are three different worksheets (named Products, Sales, and Contacts) within the workbook file.
4. Select the Products worksheet for this demonstration.
5. The Link Spreadsheet Wizard walks you through a number of different screens where you specify details such as First Row Contains Column Headings and the data type you want to apply to each column in the Excel worksheet.
6. The last screen of the Link Spreadsheet Wizard asks for the name of the newly linked table. The linked table is established as you click the Finish button and are returned to the Access environment.
As with so many other things in database development, many decisions involved in linking to external data sources are based on how the data is to be used in the application. Also, the names you provide for fields and other details have a direct impact on your application.
Linking to HTML files
Linking to data contained in HTML documents is not covered in any detail in this book because of the rather severe limitations imposed by Access on this process. For instance, 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).
All things considered, linking to arbitrary HTML documents is hit-or-miss at best. You’re much better off linking to an HTML document specifically prepared as a data source for your Access application than to try working with arbitrary HTML files.
Furthermore, if someone is going to the trouble of creating specialized HTML documents to be used as Access data sources, it is probably more reliable for them to produce comma-separated values (CSV) or fixed-width text files. Comma-separated values, where the fields in each row are separated by commas, are a very common way to move data from one application to another. CSV and fixed-width file types are discussed in the next section.
Having said that, the process of linking HTML data is very similar to linking to Excel worksheets. Use the More drop-down list in the External Data tab and select HTML Document from the list. This action opens the same Get External Data dialog box you saw when linking to Excel worksheets. And, when you select the Link to the Data Source by Creating a Link Table option and click the Browse button, 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 exactly parallel to linking to other types of data files, including providing field names and other details of the linked data (see Figure 16-9).
This book’s CD includes a very simple HTML file named CustomerTypes.html in the Chapter16 folder. The data in this file is, perhaps, overly simplistic, but it gives you the opportunity to practice linking to HTML documents. Because of the wide variety of ways that data is stored in HTML documents, it is not possible to generalize an approach to linking to HTML data. However, as you gain proficiency with the ability to link to external data sources, you may find linking to HTML a valuable addition to your Access skills.
Figure 16-9
The HTML Wizard screen that is used to name the column headings (field names) for the linked table
Linking to text files
A far more common situation is linking to data stored in plain text files. Most applications, including Microsoft Word and Excel, are able to publish data in a variety of text formats. The most common formats you’re likely to encounter are fixed-width and comma separated values (CSV).
In a fixed-width text file, each line represents one row of a database table. Each field within a line occupies exactly the same number of characters as the corresponding field in the lines above and below the current line. For instance, a Last Name field in a fixed-width text file may occupy 20 characters, while a phone number field may only use 10 or 15 characters. Each data field is padded with spaces to the right to fill out the width allocated to the field. Figure 16-10 shows a typical fixed-width file open in Windows Notepad.
Figure 16-10
A typical fixed-width text file
Comma-separated values are somewhat more difficult to understand. Each field is separated from the other fields by a comma character (,) and each field occupies as much space is necessary to contain the data. Generally speaking, there is little blank space between fields in a CSV file. The advantage of CSV files is that much more data can be contained in a CSV file because each field occupies only as much disk space as necessary to contain the data.
CSV files can be difficult to read when opened in Windows Notepad. Figure 16-11 shows a typical CSV text file.
Figure 16-11
CSV data is more compact than fixed-width text but is more difficult to read.
Text files are often used as intermediate data transfer vehicles between dissimilar applications. For instance, there may be an obsolete data-management system in your environment that is incompatible with any of the link or import data types in Access. If you’re lucky, the obsolete system is able to output either fixed width or CSV files. Linking to or importing the fixed-width or CSV files may be the best option for sharing data with the obsolete system. At the very least, much less time is required linking or importing the data that would be involved in re-keying all of the information from the obsolete system into Access.
Finally, follow these steps to link to the SalesLineItems text file:
1. Open the Chapter16 database and select the External Data ribbon.
2. Click on the Text File button to open the Get External Data dialog box.
3. Be sure the Link to the Data Source by Creating a Link Table option is selected, and then click the Browse button.
The File Open dialog box appears.
4. Locate the text file (either fixed-width, or CSV) and click the Open button.
5. Dismiss the other dialog boxes that appear.
You’ll be taken to the Link Text Wizard dialog box.
Generally speaking, Access makes pretty good guess at how the data in the file is delimited. Linking to text data involves nothing more than clicking on the Next button and verifying that Access has correctly identified the data in the file. Rather than show or describe each of the dialog boxes in the Link Text Wizard, you’re encouraged to link to Contacts_CSV.txt and Contacts_FixedWidth.txt, both included on this book’s CD.
As you’ll see when you link to these files, about the only input required from you is to provide a name for each of the fields Access finds in the text files. If you’re lucky, the text file includes field names as the first row in the text file. Otherwise, linking to text files is a very simple operation.
Splitting an Access database
Very often, developers split an Access application into two databases. One (usually called the back end) contains only tables and (perhaps) queries, while the other (called the front end) contains the forms, queries, reports, macros, and modules included in the application. Splitting an Access database into multiple pieces is an extremely important operation when building applications for multiuser environments. The front-end database is installed on each user’s machine, while the back-end database containing the tables is installed on the server. The split database arrangement has several major benefits:
• Everyone on the network shares one common set of data.
• Many people can simultaneously use and update data.
• Updating forms, reports, or other portions of the application generally means nothing more than providing a new front end to your users. The new front end can be put into service without affecting the data underlying the application, and, in fact, different users may work with different versions of the front-end database at the same time.
When creating an application for a multiuser environment, you should consider designing the objects that will be in your database, anticipating putting them into two Access databases. In general, putting all data elements (tables) in a separate database and all the visual objects (forms and reports) and code in another database usually proves more efficient. You will find it much easier to provide updates to your users by replacing the front-end database without having to worry about damaging the data underlying the application.
There are some things you just can’t do with a linked table without doing a little extra work. These tasks include finding records and importing data. By using different techniques with linked tables, however, you can do anything you can do with a single database.
If you’re starting from scratch, you first create a back-end database with just the tables for the application. You then create the front-end database and link the tables in the front end to the back end. This process is described in detail in Chapter 21. In the meantime, it is enough to understand that the primary consideration in a split database application is maintaining the linkage between the front-end and back-end databases.
Working with Linked Tables
After you link to an external table from another database, you use it just as you would any another Access table. You use linked tables with forms, reports, and queries just as you would native Access tables. When working with external tables, you can modify many of their features (for example, setting view properties and relationships, setting links between tables in queries, and renaming the tables).
One note on renaming linked tables: Providing a different name for the table inside of Access does not change the name of the file that is linked to the application. The name that Access refers to a link table is maintained within the Access application and does not influence the physical table that is linked.
Setting view properties
Although an external table is used like another Access table, you cannot change the structure (delete, add, or rearrange fields) of an external table. You can, however, set several table properties for the fields in a linked table:
• Format
• Decimal Places
• Caption
• Input Mask
• Unicode Compressions
• IME Sequence Mode
• Display Control
Setting relationships
Access enables you to set permanent relations at the table level between linked non-Access tables and native Access tables through the Relationships Builder. You cannot, however, set referential integrity between linked tables, or between linked tables and internal tables. Access enables you to create forms and reports based on relationships set up in the Relationships Builder, such as building a SQL statement used as the RecordSource property of a form or report.
Linking to external Access tables maintains the relationships that may exist between the external tables. Therefore, when linking to a back-end database, the relationships you have established in the back end are recognized and honored by the front-end database.
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 an 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.
• 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 last or first 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.
Deleting a linked table reference
Deleting a linked table from the object list is a simple matter of performing three steps:
1. In the object list, select the linked table you want to delete.
2. Press the Delete key, or right-click on the linked table and select Delete from the shortcut menu.
3. Click OK in the Access dialog box to delete the file.
Deleting an external table deletes only its name from the database object list. The actual file is not deleted.
Viewing or changing information for linked tables
Use the Linked Table Manager Wizard to update the links when you move, rename, or modify tables or indexes associated with a linked table. Otherwise, Access will not be able to find the data file referenced by the link.
Select the Database Tools ribbon and click the Linked Table Manager button. Access displays the Linked Table Manager dialog box (shown in Figure 16-12), enabling you to locate the data files associated with the linked tables in the database. Click the check box next to a linked table and click OK. Access verifies that the file cannot be found and displays a Select New Location dialog box. Using this dialog box, find the missing file and reassign the linkage to Access. If all the files are already linked correctly, clicking OK makes Access verify all the linkages associated with all the selected tables.
If you know all of the linked data sources have been moved, click the Always prompt for a new location button. Access then prompts you for the new location, and links all of the tables as a batch process. You’ll find this operation much faster than linking one or two tables at a time.
Figure 16-12
The Linked Table Manager enables you to relocate external tables that have been moved.
If the Linked Table Manager Wizard is not present on your computer, Access automatically prompts you to provide the original Office CD so that Access can install the wizard. This may happen if you didn’t instruct Office to install the Additional Wizards component during the initial installation process.
Using Code to Link Tables in Access
This section describes how to link tables to your Access application in code, instead of using the Access menus. It would be nice if you could just make the link once at development time and be done with the whole process. Occasionally, however, you may want to attach tables on the fly, to avoid losing a link. Testing your links whenever your application starts is a good practice—that way, you can keep users from getting any unplanned crashes or error messages. You’ll find some examples of these routines in this section.
The following code examples use DAO instead of ADO. For purposes such as linking tables, DAO works just as fast (actually, considerably faster), and is simpler to implement, than ADO. The reason DAO is faster than ADO for simple operations such as linking tables is because DAO does not involve the overhead associated with declaring, instantiating, and discarding ActiveX controls. Because DAO is a much simpler object model, you’ll find DAO is, arguably, a better fit for simple operations such as linking tables. ADO is definitely a better choice for complex data-management operations, but in some domains DAO still rules.
The Connect and SourceTableName properties
Open sample database for this chapter (Chapter16.accdb) and type the following in the Immediate pane of the Immediate window (use Ctrl+G to open the Immediate window):
? CurrentDB.TableDefs(“ContactLog”).Connect
you receive a Null value as the return. If, however, you type
? CurrentDB.TableDefs(“Products”).Connect
you receive a much different result. Access returns a long string that looks something like this (the path indicated at the end of this string may point to a different location):
Excel 8.0;HDR=NO;IMEX=2;DATABASE=C:\Data\AccessAutoAuctions.xls
In the first case, the ContactLog table is part of the current database, and Access finds it without any trouble. The Products_Linked table, on the other hand, is linked to an external Excel workbook file. The Connect property of the linked Products contains information that Access uses to physically locate the Excel workbook file and form a link to it.
The difference between the Connect property for the Contacts and Products tables is where the tables originate. The Connect property of an Access table found within the current database is null because the table originates in the database you’re in. There’s nothing to connect to, or more appropriately, by default, the connection always exists. However, your ODBC, Excel, and linked Access data sources will always have a Connect property that explicitly tells Access what type of data is contained in the linked data source, and where the data source file can be found.
The Connect property string is composed of a number of different parameters, some of which are required, depending on the type of external data you’re using. If you’re accessing one of the ISAM formats that Access directly supports (Excel, dBASE, FoxPro, Paradox, and so on), the connect string is much more abbreviated, taking this form:
Object.Connect=”Type;DATABASE=Path”
where Object is the name of the object variable for your TableDef, and Type is the type of database you’re connecting to, such as dBASE IV, Excel 8.0, Text, and so on.
The Path parameter can be the complete path to the file, not including the filename itself, or it might include the filename as well, depending on the type of data source. For instance, when connecting to another Access table, you include the entire path, like C:\Access\Samples\Nwind.mdb. The same is true of an Excel file.
To connect to a dBASE file, however, you only have to tell Access the path to the file, not the .dbf file itself. The difference is in whether or not the object you’ll be connecting to exists within another object, or whether the table is the file that you’re going after. That’s where the SourceTableName property comes in.
The SourceTableName property tells Access which object to take data from. If you want to connect to a dBASE file, you want your table definition to come from the .dbf itself. If, however, you’re connecting to an Excel file, you might want the table to be based on a range of cells or a single worksheet within the workbook in the .xls file, not the entire spreadsheet file. Connecting to another Access .accdb or .mdb is the same way. To link to the Customers table in Northwind.accdb, your connect string tells Access that the value of the DATABASE parameter is C:\Access\Samples\Northwind.mdb and that the SourceTableName property of your TableDef is Customers. If you want to connect to a dBASE file named NewEmp.dbf located in the root directory of C:, you tell Access that the DATABASE is C:\ and the SourceTableName is NewEmp.dbf.
The AttachExcel function (listed below) shows you how to connect to a named range within an Excel spreadsheet. To connect to a spreadsheet, you have to specify what kind of spreadsheet it is, where the spreadsheet file exists, and the range you want to connect to. You can use either a named range or a range of cells (such as A1:B20). You can also tell Access that the spreadsheet you’re connecting to contains field names in the first row. The default for this parameter is Yes.
To use the AttachExcel() function, the calling procedure must pass the spreadsheet name, the new name for the Access table, and a valid Excel range name.
The following function is located in the basAttachExcel module in Chapter16.accdb on the book’s companion CD-ROM.
The following statement invokes the AttachExcel function, linking a range named Names to a table named ExcelDemo:
AttachExcel(“”Emplist.xls””, “”ExcelDemo””, “Names”)
The AttachExcel function returns a Boolean value reporting whether the Excel file was successfully attached (True) or not (False):
Function AttachExcel( _
ByVal sFileName As String, _
ByVal sTableName As String, _
ByVal sRangeName As String _
) As Boolean
Const conCannotOpen = 3432
Const conNotRange = 3011
Const conTableExists = 3012
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim sConnect As String
Dim sMsg As String
Dim sFunction As String
On Error GoTo HandleError
AttachExcel = False
sFunction = “AttachExcel”
‘ Check for existence of worksheet:
sFileName = CurDir() & “\” & sFileName
‘ If the file isn’t found, notify
‘ the user and exit the procedure:
If Len(Dir(sFileName)) = 0 Then
MsgBox “The file “ & sFileName _
& “ could not be found”
MsgBox “Please move the file to “ _
& CurDir() & “ to continue”
Exit Function
End If
Set db = CurrentDb
‘ Create a new tabledef in the current database:
Set td = db.CreateTableDef(sTableName)
‘ Build connect string:
sConnect = “Excel 8.0;HDR=YES;DATABASE=” & sFileName
td.Connect = sConnect
‘ Specify Range Name sRangeName:
td.SourceTableName = sRangeName
‘ Append new linked table to TableDefs collection:
db.TableDefs.Append td
‘Return True:
AttachExcel = True
ExitHere:
Exit Function
HandleError:
Select Case Err
Case conCannotOpen
sMsg = “Cannot open “ & sFileName
Case conTableExists
sMsg = “The table “ & sTableName & _
“ already exists.”
Case conNotRange
sMsg = “Can’t find the “ & sRangeName & “ range.”
Case Else
sMsg = “Error#” & Err & “: “ & Error$
End Select
MsgBox sMsg, vbExclamation + vbOKOnly, _
“Error in Procedure “ & sFunction
AttachExcel = False
Resume ExitHere
End Function
Connect strings and source table names are more involved when you’re using ODBC data sources. For instance, when you connect to a SQL Server ODBC data source, you have the option of specifying the type of source you’ll be using (ODBC), the DSN (data source name), the application you’re using, the table within the data source that contains the data you want, the workstation using the application, and a user ID and password. Not all of these parameters are available to every ODBC data source, so you need to consult your ODBC driver manual to find out what you can and can’t use.
By the way, you might not want to hard-code a user ID and password in your connect string but instead use some combination of Access and a customized security setup that allows you to capture a user’s ID and password when the user logs in to your application and then pass those values dynamically.
One final example we’ve included in this section is one that shows you how to connect to a text file. As we mention in the “Linking to text files” section, earlier in this chapter, you can link to delimited or fixed-width text files. Linking to a text file follows the same process as the previous examples; the biggest difference is the DSN parameter. Before you can link to a text file, you must create an import specification that tells Access what the file looks like.
In previous versions of Access, you created import/export specs only when you imported or exported fixed files; but Access 2007 lets you create a spec for delimited files as well. If you use the Import Wizard, Access creates an import specification for you. The connect string for a text file is the name of the import spec you’ve created. The Database parameter is the path to the file, and the SourceTableName property is the filename you want to link to, without the file extension.
The following function is located in the basLinkText module in Chapter16.accdb on this book’s companion CD-ROM.
Function LinkText( _
ByVal sFileName As String, _
ByVal sDSN As String, _
ByVal sFMT As String, _
ByVal sHDR As String, _
ByVal sIMEX As String, _
ByVal sTableName As String _
) As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim x As Integer
Dim sType As String
Dim sPath As String
Dim sPathAndFileName As String
Dim sDatabase As String
Dim sConnect As String
Dim sMsg As String
Dim sFunction As String
Const conTableExists = 3012
On Error GoTo HandleError
LinkText = False
sFunction = “LinkTxt”
‘ Check for existence of file:
sPath = CurDir() & “\”
sDatabase = sPath & sFileName
If Len(Dir(sDatabase)) = 0 Then
MsgBox “The File “ & sFileName & _
“could not be found”
MsgBox “Copy the file to “ & CurDir() _
& “ to continue”
Exit Function
End If
‘ Create Tabledef:
Set db = CurrentDb
Set td = db.CreateTableDef(sTableName)
sType = “Text;”
sDSN = “DSN=” & sDSN & “;”
sFMT = “FMT=” & sFMT & “;”
sHDR = “HDR=” & sHDR & “;”
sIMEX = “IMEX=” & sIMEX & “;”
sDatabase = “DATABASE=” & sPath
sConnect = sType & sDSN & sFMT & sHDR & sIMEX & sDatabase
td.Connect = sConnect
td.SourceTableName = sFileName
db.TableDefs.Append td
LinkText = True
ExitHere:
Exit Function
HandleError:
Select Case Err
Case conTableExists
sMsg = “The table “ & sTableName _
& “ already exists.”
Case Else
sMsg = “Error#” & Err & “: “ & Error$
End Select
MsgBox sMsg, vbExclamation + vbOKOnly, _
“Error in Procedure “ & sFunction
LinkText = False
Resume ExitHere
End Function
Assuming you have created an import link specification named EmployeeImport Link Specification, the following statement uses the LinkText() function to link data from the Empimp.txt text file to a new table named EmployeeLink.
The sample database, Chap16Start.accdb, already contains this import link specification and the LinkText() function.
The text file ImpFixed.txt, which contains fixed-length data, is also found in the Chapter16 folder on the companion CD. Use a statement like the following to link ImpFixed.txt to a table named EmployeeLink:
LinkText(“ImpFixed.txt”, _
“EmployeeImport Link Specification”, _
“Fixed”, “NO”, “2”, “EmployeeLink”)
To import a comma-delimited text file named ImpDelim.txt (also found on the companion CD-ROM) for which you have created a corresponding import link specification, you can use the following statement:
LinkText(“ImpDelim.txt”, _
“EmployeeImport Link Specification Delimited”, _
“Delimited”, “NO”, “2”, “EmployeeLink2”)
As you can see, there are dozens of combinations you can use when linking to external data sources. The connect strings for each can get a little confusing, but there is a way to make connecting easy. If you pretend you’re an end user and use the wizards, the process can be a lot easier. Once you step through the process of linking the table you want using the Link Wizard, open the Debug window and query the Connect and SourceTableName properties of the table you’ve linked. Once you do, you’ll have all you need to build the VBA code for doing the same thing programmatically. Just copy the connect string from the Debug window and paste it into your procedure.
Checking links
You (or, more accurately, your users) will at some point encounter a situation where a linked table in one of your applications becomes unavailable. For example, suppose your application links to a SQL Server database and the network goes down. One of your users, who does not know the network is down, sits down at his workstation and tries to pull up your application. As soon as the attempt is made to access data from the attached table, an error occurs and your uninformed user panics. Here’s another common scenario: Suppose your application is linking to an Excel spreadsheet, but someone decides to clean up a directory and moves, renames, or deletes the spreadsheet. Again, an error occurs when someone tries to access data from the linked table. You may not be able to prevent these situations, but you can plan for them ahead of time.
The following function, CheckLinks, should probably be run as a startup routine for your application, or in addition to any procedures you run when your application is accessed. You can pass the function the name of an attached table, and test to see if the link is still valid. All the procedure does is try to open the table as a recordset. If the OpenRecordset method fails, either the table doesn’t exist in the database or the link has been lost. All this function has to do is flash a descriptive message to the user and a return value to announce that the application should proceed no further.
The following function is located in the basTestLinks module in Chapter16.accdb on the book’s companion CD-ROM.
Function TestLink(sTablename As String) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iStartODBC As Integer
Dim iEndODBC As Integer
Dim sDataSrc As String
Dim iODBCLen As Integer
Dim sMessage As String
Dim iReturn As Integer
On Error GoTo HandleError
Set db = CurrentDb
‘Open a recordset to force an error:
Set rs = db.OpenRecordset(sTablename)
‘If the link is valid, exit the function:
TestLink = True
ExitHere:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function
HandleError:
‘If the link is bad, determine what the problem
‘is, let the user know, and exit the function:
Select Case Err
Case 3078 ‘Table doesn’t exist:
sMessage = “Table ‘“ & sTablename _
& “‘ does not exist in this database”
Case 3151 ‘Bad link
‘Extract the name of the odbc DSN
‘to use in your custom error message:
iStartODBC = InStr(Error, “to ‘“) + 4
iEndODBC = InStr(Error, “‘ failed”)
iODBCLen = iEndODBC - iStartODBC
sDataSrc = Mid$(Error, iStartODBC, iODBCLen)
sMessage = “Table ‘“ & sTablename _
& “‘ is linked to ODBC datasource ‘“ _
& sDataSrc _
& “‘ which is not available at this time”
Case Else
sMessage = Err.Description
End Select
iReturn = MsgBox(sMessage, vbOKOnly)
‘Return failure:
TestLink = False
Resume ExitHere
End Function
Summary
Linking to external data sources is an essential requirement for many access applications. Microsoft Access is equipped to deal with virtually any type of external data, including obsolete database types such as dBASE and Paradox as well is more modern data types such as HTML and XML.
With few exceptions, linking to virtually any external data source requires very few steps on the part of a developer. The Access linking wizards are very similar, regardless of the data type involved in the link operation. The code required to automatically link to external data sources is not extensive and is easily incorporated into Access applications. Access also provides tools such as the Linked Table Wizard to help you manage linked tables in your applications.
Chapter 17 deals with the important topic of importing data into Access applications. Although the process is very similar to linking to external data sources, importing moves the data into an Access database permanently. As you’ll see in Chapter 17, virtually the same steps are required to import data as were required to link to external data.