| Access and other Microsoft programming platforms, such as Visual Studio 6.0 and increasingly Visual Studio .NET, dominate today's database front-end development market. Thus the importance of Access's import/link support for legacy desktop database files has decreased as use of Paradox, dBASE, and FoxPro have declined, especially for new projects. Paradox, dBASE, Clipper, and FoxPro now qualify as legacy data formats, although today's diehard Visual FoxPro programmers certainly would argue this point. Conventional desktop database development applications maintain each table in an individual file. Each file contains a header followed by the data. A header is a group of bytes that provides information on the file's structure, such as the names and types of fields, number of records in the table, and file length. When you create a table file in dBASE, Visual FoxPro, or Paradox, for example, the file contains only a header. As you add records to the file, the file size increases by the number of bytes required for one record, and the header is updated to reflect the new file size and record count. Desktop RDBMSs create a variety of supplemental files, some of which are required to import, link, or export RDBMSs: Visual FoxPro and dBASE .dbf files store memo-type data in a separate .dbt file. If a FoxPro or dBASE table file contains a memo field, the .dbt file must be available. If the .dbt file is missing, you can't import or link dBASE or Visual FoxPro tables that contain a memo field. Use of .ndx (dBASE III), .mdx (dBASE IV+), or .idx or .cdx (FoxPro) index files is optional. You always should use index files when you have them. If you don't link the index files when you link an indexed .dbf table file, modifications you make to the linked tables aren't reflected in the index, which causes errors to occur when you try to use the indexed tables with dBASE. Linking an indexed dBASE table requires the Borland Database Engine (BDE) described in the following Caution. Paradox stores information about the primary-key index file (.px) in the associated table (.db) file; the .px file for the .db file must be available for Access to open a Paradox .db file for updating. Access links the .px file automatically if it exists. Like dBASE, Paradox stores memo-type data in a separate file with a .mb extension. Linking an indexed Paradox table also requires the BDE.
Tip  | You must have exclusive access to the dBASE file when you first create the link; multiuser (shared) access is supported thereafter. For more information see the "Using dBASE Data with Access 2002 and Jet" Knowledge Base topic at http://support.microsoft.com/default.aspx?scid=kb;EN-US;290867. Office 2003 installs Jet 4.0 Service Pack 7, which doesn't support multiuser access to Paradox files; you must have exclusive access to the Paradox file whenever you have the linked file open. See the "Using Paradox Data with Access 2002 and Jet" at http://support.microsoft.com/default.aspx?scid=kb;EN-US;286246. |
Caution You can't attach dBASE 7-8 or Paradox indexes to linked .dbf or .db files unless you have the Borland Database Engine (BDE) from Borland (formerly Inprise) Corporation installed on your computer. The inability to attach indexes means that any records you add or in which you change values of indexed fields no longer are accessible from dBASE 7-8 or Paradox applications that rely on table indexes. (Almost all commercial dBASE and Paradox applications use indexes.) You need the BDE to export or create read or write links to Paradox 7-8 and dBASE 7-8 files. You can obtain more information on the BDE, which is included with the Delphi 4.0 development platform, at http://info.borland.com/devsupport/bde/. If you have a BDE version earlier than 5.01, you can obtain a no-charge upgrade to version 5.1.1 at http://info.borland.com/devsupport/bde/bdeupdate.html.
All supplemental files must be in the same folder as the related database file to be used by Access. Tip Create a new folder to store the tables you import or export. The default folder for exporting and importing files is \My Documents in all current Windows operating systems. If you intend to import or export a large number of files, change the Default Database Folder entry in the General page of the Options dialog (choose Tools, Options).
Dealing with PC Database Files Access can import and export, subject to the preceding limitations, the following types of database table files used by the most common PC database managers: dBASE .dbf table and .dbt memo files as well as dBASE III .ndx and dBASE IV, 5.0, and 7.0 .mdx index files dBASE III+ files are a common denominator of the PC RDBMS industry. Most PC RDBMSs and all common spreadsheet applications can import and export .dbf files; the most popular formats are dBASE III and IV. Some of these RDBMSs can update existing .ndx and .mdx index files, and a few RDBMSs can create these index files. Access 2003 links and exports .ndx and .mdx indexes only if you have the BDE installed. You must have version 5.01+ of the BDE to import, export, or link dBASE 7.0/8.0 tables. When this book was written, the current version was 5.1.1. Visual FoxPro .dbf table and .dbc database container files Access 2003 requires the Microsoft Visual FoxPro ODBC driver (VFPODBC.dll) for import and link operations. Prior to OLE DB and ActiveX Data Objects (ADO), Open Database Connectivity (ODBC) was Microsoft's preferred technology for connecting to client/server databases and other data sources. Note | Windows 2000 installs VFPODBC.dll as a component of Microsoft Data Access Components (MDAC) 2.5, but Windows XP and .NET Server install MDAC 2.6, which doesn't include the Visual FoxPro driver. You can download and run version 6.01.8629 of the FoxPro ODBC driver installer (Vfpodbc.msi) from http://msdn.microsoft.com/vfoxpro/downloads/addons/odbc.asp. This version, which is the latest (and final) FoxPro ODBC driver that Microsoft offers, has serious limitations when exporting Jet tables to FoxPro .dbf or .dbc files. You receive an "Error -7778" message if you attempt to use a file data source. The Microsoft Knowledge Base article at http://support.microsoft.com/support/kb/articles/q212/8/86.asp confirms the problem. If you use a Machine data source, the filename and all field names must be eight characters or less, and field names can't contain spaces. The Knowledge Base article at http://support.microsoft.com/support/kb/articles/q237/8/19.asp has more information on this issue. |
Paradox 3.x, 4.x, 5.x, 7.x, and 8.x .db table, .mb memo, and .px primary-key files Access 2003 supports importing and exporting Paradox 3.x, 4.x, and 5.x .db and .mb files. Access doesn't generate .px files when you export a .db file. You can link Paradox 3-5 files with or without .px indexes, but if you don't have a .px index, the linked table won't open in Datasheet view. Note If you work in a multiuser environment, you must have exclusive access to the file you intend to import. No one else can have this file open when you initiate the importing process, and everyone else is denied access to the file until you close the Import dialog.
Caution Make sure that you work on a backup, not on the original copy of the linked file, until you're certain that your updates to the data in the linked table are valid for the existing database application.
Linking and Importing External ISAM Tables ISAM is an acronym for indexed sequential access method, the architecture used for all desktop RDBMS tables. To link or import a dBASE or Paradox file as a table in Access 2000 or 2002 file format, follow these steps: Note Linking an external file to an Access database was referred to as attaching a table in Access 95 and earlier. Don't confuse linking an external file to an Access database with OLE Object field links to image or sound files; when you link an external file, you just give Access information about the external file necessary to open, read, and modify the data in that file.
 If you have a test database that you can use for this procedure, open it, and skip to step 4.
If you don't have a test database, create a sample to use throughout this chapter. Click the Blank Database link of the task pane's New File page to display the File New Database dialog. Navigate to the folder in which to store the new database, type a name, such as LinkTest.mdb, in the Filename text box and click Create. Access creates and tests the new database.  In this example, you link an external table to the database. Choose File, Get External Data, Link Tables or click the Link icon to open the Link dialog; the Link dialog is a variation of your operating system's Open dialog. If you choose File, Get External Data, Import, the Import dialog opens.
Open the Files of Type drop-down list to select the file type you want to link, as shown in Figure 8.1. (If you have a Paradox table with a primary key index to link, select Paradox. Otherwise, select dBASE III, dBASE IV, DBase 5, or another database type as appropriate to the format of your table file.) Figure 8.1. Navigate to the folder that holds the database file to link and select the type of database file in the drop-down list.  Note  | The \Seua11\Chaptr08 folder of the accompanying CD-ROM has subfolders that contain example dBASE III and IV, FoxPro 6, Paradox, and text files that you can import or link to your test database. All examples in this chapter assume that you've installed the sample database files to C:\Program Files\Seua11\Chaptr## folders and that the read-only attribute of the files isn't set. |
Double-click the name of the table you want to link or import (or click the name to select it and then click the Link button). Access supplies the standard extensions for dBASE and Paradox table files. If the Paradox file you choose is encrypted and requires a password to decrypt it, the Password Required dialog opens. Type the password and press Enter. After you successfully link or import the file, a dialog confirms this operation (see Figure 8.2). If you link more than one table with the same name, Access automatically appends a sequential digit to the table name. If a memo or other related file is missing, you receive an error message at this point. Figure 8.2. This message box confirms the linking process.  Note The Link (or Import) dialog remains open at this point. If you want to link or import additional external tables to this database (most Paradox and xBase databases consist of several separate table files), repeat steps 6 through 9 for all the files you want to link or import. If you're linking external Access tables, you can select all the tables you want to link at once by clicking each one. In the Link dialog, click Close. The table(s) you linked or imported now are listed in the Database window. If you linked a file, Access adds an icon that shows the type of database table and an arrow to indicate the table is linked. Figure 8.3 illustrates linked dBASE CUSTOMER, Paradox Employee, and dBASE EMPLOYEE1 tables. Access added the "1" suffix, because the Paradox Employee table was present before adding the dBASE EMPLOYEE table. Figure 8.3. Linked tables are identified in the Database window by an icon for the file type (dB for dBASE and Px for Paradox) with an arrow to represent the link.  Double-click the entry for the table you linked to display the records in Table Datasheet view (see Figure 8.4). Figure 8.4. The EMPLOYEE1 table is linked from a pair of dBASE III tables EMPLOYEE.DBF and EMPLPLOYEE.DBT. The EMPLOYEE.DBT file contains the data for the Notes field and doesn't appear in the Database window. 
Note Images stored in the sample Employee.mb file in the ...\Chaptr08\Paradox folder are in Access OLE Object format, so you can open the images in Windows Paint by double-clicking a Bitmap Image cell.
After you link an external file as a table, you can use it almost as though it were a table in your own database. If you don't have the BDE installed, linked dBASE 5 and later and all Paradox tables are read-only. A general limitation is that you can't change the structure of a linked table: field names, field data types, or the Field Size properties. There is no limitation on changing the structure or properties of an imported table. Note Although you can't change field properties for linked tables, you can change the name of the attached table within this database only. Select the link, press F2, and type the new name for the table. The name for the table (called an alias) is changed only in the current Access database and not in the native database.
Linking Visual FoxPro Tables with ODBC You can use the ODBC drivers provided with Office 2003 to link Visual FoxPro databases or tables to Jet databases. Data source is a synonym for database when you use the ODBC Application Programming Interface (API) to link tables. Tip Windows 2000 installs 32-bit Excel, FoxPro, Paradox, Access, dBASE, and Text file ODBC drivers, plus the SQL Server and Oracle client/server drivers. As mentioned in the earlier "Dealing with PC Database Files" section, Windows XP doesn't install the FoxPro ODBC driver. The ODBC Data Source Administrator Control Panel tool is installed by the Windows operating system. Windows XP/2000+ call the ODBC Manager's icon Data Sources (ODBC), which you find in Control Panel's Administrative Tools folder. You can determine which drivers are installed by clicking the Drivers tab of the Administrator dialog.
Follow these steps to link Visual FoxPro 6.0 table(s) to an Access database via ODBC: With an Access database open, choose File, Get External Data, Link Table to display the Link dialog (refer to Figure 8.1). Select ODBC Databases in the Files of Type drop-down list. Access closes the Link dialog and displays the Select Data Source dialog. If you want to create a file to define a data source (except for FoxPro tables) that you can share with others, accept the default File Data Source page. This example uses a Machine Data Source installed by the Visual FoxPro ODBC driver, so click the Machine Data Source tab. Select Microsoft Visual FoxPro Tables (for a .dbf file) in the Data Source Name (DSN) list, and click OK to open the Configure Connection dialog. If you want to link tables from a .dbc file, select the Visual FoxPro Database DSN, as shown in Figure 8.5, and click OK to close the dialog. Figure 8.5. Select the ODBC driver for the type of database to link from the list of drivers installed on your machine.  Note If you haven't installed the FoxPro ODBC driver, you will receive a Visual FoxPro ODBC Error message at this point. If you chose the Visual FoxPro Database DSN, select the Visual FoxPro Database (.DBC) option, click Browse to open the Select Database dialog, navigate to the folder containing the .dbc file, and select the file in the list (see Figure 8.6). Otherwise Accept the Free Table Directory, and click Browse to display the Select Directory Containing Free Tables dialog. If necessary, use the Drives and Directories lists to navigate to the folder in which your FoxPro files are stored in the Folders list. In either case, click Open and OK to display the Link Tables dialog. Figure 8.6. Select the .dbc file or the folder containing the FoxPro .dbf file(s) to configure the ODBC connection with the Office 2003 FoxPro driver.  The Link Tables dialog lists the FoxPro tables in the folder you selected in step 4. Click each table name to select it, or click Select All. After you select all the tables that you want to link (see Figure 8.7), click OK. Figure 8.7. The Link Tables dialog lists all FoxPro tables in the folder or in a .dbc container file. Select the table(s) you want to link to your Jet database, and click OK.  If any of the table(s) you selected don't have a primary-key index, the Select Unique Record Identifier dialog opens. If you want to update the data in the ODBC-linked table without a primary-key index, you must select a field (or combination of fields) that creates a unique record identification for each row in the table essentially, you create a surrogate primary key for the linked table. To select a field, click it, and then click OK. In the example in Figure 8.8, the primary-key field of the customers table is customerid. Figure 8.8. If the folder with the tables or the .dbc file doesn't include a primary-key index for any of the tables you select to link, you must select a field to act as a surrogate primary key in the Select Unique Identifier dialog. 
Note All FoxPro 6.0 tables in the \Program Files\Seua10\Chaptr08\FoxPro 6 folder have primary key indexes, so the Select Unique Record dialog doesn't appear when you link these tables.
Your linked Visual FoxPro tables appear in the Database window (with the ODBC globe turned to display Africa), as shown in Figure 8.9. Any table linked by ODBC displays the globe icon. Double-click the table icon to display your newly linked Visual FoxPro table (see Figure 8.10). By default, Visual FoxPro table and field names appear in lowercase. The maximum length of a FoxPro or dBASE field name is 10 characters. Figure 8.9. The FoxPro contacts and products files are linked to the Jet database. The two dBASE tables and the Paradox table have been renamed in this figure.  Figure 8.10. The linked FoxPro products table is updatable, as indicated by the tentative append (*) record at the end of the table records.   | Access detects problems with linked or imported tables that might cause errors when you try to use the tables with Access. Importing and linking errors are the subject of the "Troubleshooting" section near the end of this chapter. |
Dealing with Images in External Database Files Most database managers designed for Windows include some form of graphics field data type. Early versions of Paradox, for example, provide a special field data type for graphics; later versions support OLE objects. Although early versions of dBASE lack a field data type for graphics, third-party software firms publish applications that let you store images in dBASE memo fields. Various add-on applications for desktop RDBMSs let programmers display and edit graphic images. The images usually are in individual files, but a few third-party applications continue to place images in memo files. When you try to import or link desktop database files containing images or other binary data, you might receive an error message that the memo file is corrupted or that you can't import the .db or .dbf file that contains the offending memo or graphics field. In rare cases usually involving tiny images you can import the .dbf and .dbt files, but you see random characters in the Access memo field. With Paradox tables, the graphics or binary fields disappear from the table. The simplest approach to dealing with graphics files missing from imported tables is to convert the individual files to Graphic Interchange Format (.gif) or Joint Photographic Experts Group (.jpg) format, and then use Windows Paint to import the images into an OLE Object field. The following procedure uses the employees table imported from the fitch_mather.dbc FoxPro 6 database of the preceding section, and the nine bitmap files, Empid1.bmp Empid9.bmp, in the \Program Files\Microsoft Office\Office 10\Samples folder. Tip The Empid#.bmp files are relatively small, but .bmp files of large images with 24-bit or 32-bit color depth can become very large. Using .gif or .jpg format to compress the images before you insert them in an OLE Object field saves a substantial amount of disk space.
To add an OLE Object field and images from files to an imported (or new) table, do this:  Open the table in Design view, select the field below the location for the new OLE Object field, and press Insert to add an empty field. This example uses the employees table imported from the FoxPro Fitch&Mather.dbc database.
Type the Field Name, emp_photo for this example, and select OLE Object as the Data Type (see Figure 8.11). Figure 8.11. Add an OLE Object field to the imported table to hold image data in OLE 2.0 format or to create a link to external image files.   Return to Datasheet view; saving the design changes, right-click the OLE Object cell in the first row, and choose Insert Object to open the Microsoft Access (formerly Insert Object) dialog.
Select the Create From File option, click Browse to open the Browse dialog, and navigate to the folder that contains your image files (\Program Files, Microsoft Office\Office 10\Samples for this example). Double-click the image file you want to embed in the field (Empid1.bmp for the first image), which adds the well-formed path to the file in the text box (see Figure 8.12). Figure 8.12. By default, the bitmap data is included in the OLE Object field. Select the Link check box to maintain the image data in the source .gif, .jpg, or .bmp files.  Note Instead of embedding the image in the database, you can create a link to the image's source file by selecting the Link check box. Linking reduces the size of the .mdb file, but requires a permanent path to the source image files. If a network server stores the image files, the path might change. If this happens, you receive an error when attempting to open the linked file.  Click OK to embed or link the image, which adds a Bitmap Image value to the cell. Double-click the cell to open the image in Windows Paint (see Figure 8.13). You can edit the image in Paint; Access automatically saves your changes when you close Paint and return to the table.
Figure 8.13. Double-clicking a Bitmap Image cell opens the image for editing in Windows Paint. If you've associated a different OLE 2.0-compliant application to the file type, the image opens in that application.  Repeat steps 4, 5, and 6 for each image to add, selecting the row appropriate to the image file. | The employees table of the LinkTest.mdb database in the \Seua10\Chaptr08 folder of the accompanying CD-ROM includes several embedded bitmap images. |
Converting Field Data Types to Jet Data Types When you import or link a file, Access reads the header of the file and converts the field data types to Access data types. Access usually is successful in this conversion because it offers a greater variety of data types than most of the other widely used PC RDBMSs. Table 8.1 shows the correspondence of field data types between dBASE, Paradox, and Access files. Table 8.1. Field Data Type Conversion Between Access and Other RDBMSsdBASE III/IV/5 | Paradox 3.x, 4.x, 5.0 | Access |
|---|
Character | Alphanumeric | Text (Specify Size property) | Numeric, Float[*] | Number, Money, BCD[*] | Number (Double) | | | | Number (Single) | | | | Number (Byte) | | | Short Number | Number (Integer) | | | Long Number | Number (Long) | | | AutoIncrement | AutoNumber | Logical | Logical | Yes/No | Date | Date, Time, Timestamp[*] | Date/Time | Memo | Memo, Formatted Memo, Binary[*] | Memo | | | OLE | OLE Object |
[*] Sometimes two types of field data, separated by commas, are shown within a single column in Table 8.1. When Access exports a table that contains a data type that corresponds with one of the two field data types, the first data type is assigned to the field in the exported table. The Float data type is available only in dBASE IV and 5.
Tip If you're importing tables, you can change the field data type and the Field Size property to make them more suitable to the type of information contained in the field. When you change a data type or Field Size, however, follow the precautions noted in Chapter 5, "Working with Jet Databases and Tables." Remember that you can't change the field data type or Field Size property of linked tables. You can, however, use the Format property with imported or linked tables to display the data in any format compatible with the field data type of imported or linked files. You can change any remaining properties that are applicable to the field data type, such as validation rules and text. By using the Caption property, you can give the field a new and more descriptive name.
 To review field properties of Jet databases, see "Choosing Field Data Types, Sizes, and Formats," p. 162.
Using the Linked Table Manager Add-in to Relink Tables Moving linked files to another folder or logical drive causes the existing links to break. Access provides an add-in assistant known as the Linked Table Manager to simplify relinking tables. If you move a Jet, dBASE, FoxPro, or Paradox file that provides a table linked to a Jet 4.0 database, choose Tools, Database Utilities, Linked Table Manager. The Linked Table Manager's window lists all tables linked to the database. The list also displays the path to the database containing the linked table(s) at the time the link was created, with the exception of the path to files or databases linked by ODBC. Click the check box of the file(s) whose location(s) might have changed (see Figure 8.14). Figure 8.14. The Linked Table Manager handles recreating links to tables or databases that have moved since you created the original links to a Jet database.  Tip You also can view the path to the folder containing a linked table by opening the linked table in Design view, opening the Table Properties window, and scrolling through the contents of the Description text box.
Click OK to display the Select New Location of TableName dialog shown in Figure 8.15. (If your linked files haven't moved, Mark the Always Prompt for New Location check box to open the dialog.) Navigate to the folder where the table or database is located; then double-click the new link reference and close the dialog. If Access successfully refreshes the table links, it displays a dialog saying so; click OK to close the success message dialog. Click the Close button of the Linked Table Manager to close the add-in. Figure 8.15. The Select New Location of TableName dialog lets you substitute another folder or database file for the broken link.  Note If you select a table linked by ODBC, the ODBC Manager's Select Data source dialog opens so you can recreate the data source for the linked table or database.
Tip The Linked Table Manager can refresh links only for tables that have been moved to another disk or folder the table must have the same name. If the linked table's file was renamed, you must delete the table link from your Access database and relink the table under its new name.
Importing Versus Linking Database Files as Tables The preceding examples demonstrate the differences between the behavior of Access with linked and imported database files. You should link tables contained in another database file if any of the following conditions exist: You use another RDBMS to modify the file in any way. The database or file is resident on another computer, such as a server, and its size is larger than fits comfortably on your fixed disk. You observe the recommended database application development practice of maintaining separate .mdb files for tables and your application's objects.
You should import a table when one of the following conditions exists: You've decided on Access as your database development platform and have mothballed existing dBASE, Paradox, or FoxPro applications. You're developing an application and want to use data types or Field Size properties different from those Jet has chosen for a linked table. You or the users of your application don't have online access to the required database files and can't link them. You want to use a primary-key field different from the field specified in a desktop database or client/server table. This situation can occur when the structure of one or more of the files you plan to use seriously violates one or more of the normalization rules described in Chapter 4, "Exploring Relational Database Theory and Practice." You need Access to allow duplicate values in your table when a primary-key field of a linked table precludes duplicate values.
In most cases, importing a table is a one-way process. After you import and modify the table, it's unlikely that applications in the source RDBMS will be able to use an exported version of the modified table. |
