Understanding How Access Handles Tables in Other Database File Formats

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, FoxPro, or Paradox, for example, the file contains only a header. As you add records to the file, the file grows 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:

• 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.

• dBASE and FoxPro store memo-type data in a separate .dbt file. If a dBASE table file contains a memo field, the .dbt file must be available. If the .dbt file is missing, you cannot import or link dBASE or 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 or FoxPro.

All supplemental files must be in the same folder as the related database file to be used by Access.

The header of an Access 97 .mdb file differs from conventional PC RDBMS files in that an .mdb header consists of a collection of system tables that contain information on all the tables, indexes, macros, and Access VBA functions and procedures stored in a single Access file. The Access system tables also contain information on the location and characteristics of other PC RDBMS files that you linked to your Access database. Access's system tables are similar to the tables used in client/server databases that maintain information on the content of database devices (files), plus the databases and tables contained in the devices.

Identifying PC Database File Formats

Access can import, link, and export the following types of database table files used by the most common PC database managers:

• dBASE .dbf table and .dbt memo files, and dBASE III .ndx and dBASE IV .mdx index files. dBASE III and IV files and indexes are the standard language of the PC RDBMS industry. Most PC RDBMSs and all common spreadsheet applications can import and export .dbf files. Most of these RDBMSs can update existing .ndx and .mdx index files, and some RDBMSs can create these index files.

  The .dbf file structure is native to other xBase clone applications such as FoxPro, but not all these RDBMSs create fully compatible dBASE file structures. Compilers such as CA-Clipper have their own native index-file structures, but they can use .ndx indexes when necessary. The capability to use .mdx multiple-index files is less widespread.

  Access can link and create .ndx and .mdx files. Access updates both types of dBASE index files when you edit or add records to a linked .dbf file. The later section Setting Primary Keys and Using Index Files discusses index files in linked tables. When you export an Access table with a memo field, a .dbt memo file with the same name you assign to the dBASE file is created.

• FoxPro 2+ .dbf table files. You can import, export, and link FoxPro 2+ .dbf files and files created by earlier versions of FoxPro. The procedures for handling FoxPro 2+ .dbf files are the same as the procedures used for dBASE III and IV. Access maintains the currency of FoxPro 2+ .idx (single) and .cdx (multiple) index files.

• Paradox 3.x, 4.x, and 5.0 .db table and .px primary key files. You can link Paradox for DOS 3.x and 4.x table and index files, and those files created by Paradox for Windows 5.0. The following section presents the specific limitations applicable to Paradox files.

Most applications that use table and index files also use the standard file extensions presented in the preceding paragraphs. The dBASE memo file, for example, requires a standard extension—.dbt. Using the standard extensions for all file types, however, is not a requirement. Some developers of xBase applications disguise their files by using arbitrary extensions. You may have to do some detective work to determine which files contain data and which are indexes.

Linking and Importing External Tables

To link or import an xBase or Paradox file as a table in an open Access database, such as Northwind.mdb, follow these steps:

1.	Click the Show Database Window toolbar button or choose Window, 1 Data-base. Access doesn't require that all open tables be closed before you link or import a table.
2.	If you have a test database that you can use for this procedure, click the Open Database toolbar button or choose File, Open Database; then select the test database, open it, and skip to step 5.
3.	If you don't have a test database, create a sample to use throughout this chapter. Click the New Database toolbar button or choose File, New Database to display the New dialog.
4.	Double-click the Blank Database icon in the New dialog; Access displays the File New Database dialog. Type a name, such as Mdb Test.mdb, in the File Name text box and click Create. You must wait while Access creates and tests the new database.
5.	In this example, you link an external table to the database. Choose File, Get External Data, Link Tables. The Link dialog appears (see Figure 7.1); the Link dialog is a variation of the common Windows 95 and Windows NT 4.0 Open dialog. If you choose File, Get External Data, Import, the Import dialog appears.
6.	Use the Files of Type drop-down list to select the file type you want to link. (If you have a suitable Paradox table to link, select Paradox. Otherwise, select dBASE III, dBASE IV, or another database type as appropriate to the format of your table file.)
7.	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, FoxPro, and Paradox table files. If you're linking a .dbf file that has indexes, the Select Index Files dialog appears. The later section Setting Primary Keys and Using Index Files explains how to use the Select Index Files dialog. Figure 7.1. The Link dialog used to select external files for linking to an Access database. Note You can link Access 1.x, Access 2.0, and Access 95 files to an Access 97 database. Linking Access 1.x, 2.0, and 95 files solves the problem of not being able to convert an Access 97 mdb file to any previous Access version .mdb format for backward compatibility, which is another reason to always use one .mdb file for tables and another .mdb file for your application database objects.
8.	If the file you choose is encrypted (coded) and requires a password to decrypt it, the Password Required dialog appears. Type the password and press Enter.
9.	After you successfully link or import the file, a dialog appears, confirming this operation (see Figure 7.2). If you link more than one table with the same name, Access automatically appends a sequential digit to the table name. Figure 7.2. The message that appears after successfully linking an external table to a database. The Link (or Import) dialog remains open; if you have additional external tables you want to link or import to this database—most Paradox and xBase databases consist of several separate table files—repeat steps 6–9 for all the files you want to link or import. If you are linking external Access tables, you may select at once all the tables you want to link by simply clicking each one.
10.	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 indicates the type of database table and an arrow that indicates that the table is linked (see Figure 7.3). Figure 7.3. The special icons displayed in the Database window indicating linked tables.
11.	Select the table you linked, and then click the Open button to display the records in Table Datasheet view (see Figure 7.4). Alternatively, you can double-click the table name.

After you link an external file as a table, you can use it almost as if it were a table in your own database. The only general limitation is that you cannot change the structure of a linked table: field names, field data types, or the Field Size properties. In linked Paradox files, Access prevents you from changing a table's primary-key field that previously was defined because this property determines the contents of the associated .px index file.

Solving Problems with Importing or Linking Files

Access detects problems with linked or imported tables that may cause errors when you try to use the tables with Access. The following sections describe these problems and how to overcome most of them.

The Incorrect Password Dialog. If you enter a wrong password or just press Enter, Access informs you that it cannot decrypt the file. You do, however, get another opportunity to enter the password or click Cancel to terminate the attempt (see Figure 7.5).

The Null Value in Index Dialog. Occasionally, older Paradox .px index files don't have an index value for a record; when this situation occurs, you see a warning dialog with the message Can't have Null value in Index. Usually, you can disregard the message and continue linking or importing the file. The offending record, however, may not appear in the table; fixing the file in Paradox and starting over is better than ignoring the message.

The Missing Memo File Dialog. Both dBASE and Paradox use additional memo files to store the data from memo fields in a particular table. dBASE memo files have the .dbt file type, and Paradox memo files have the .mb file type. Access correctly decides that it can't import or link an external table if it can't open the table's associated memo file—either because the memo file doesn't exist, isn't in the same folder as the table with which it is associated, or contains non-text data.

If the table you're trying to link or import is a dBASE table, Access displays the error dialog shown in Figure 7.6. If the table you're trying to link is a Paradox table, however, you receive the less informative error message shown in Figure 7.7. For assistance in solving the problem, click the Help button. To close the error dialog, click OK; Access then cancels the linking or importation.

The Graphics Field Type Restriction. If the successful-link dialog doesn't appear, your table or its accompanying dBASE or Paradox memo file probably contains a graphics field type. The following section discusses how to modify files with graphics content so that you can link or import them as Access tables.

Dealing with Graphics in External Files

Most database managers designed for Windows include some form of graphics field data type. Superbase, Paradox 4.x, and Paradox for Windows provide a special field data type for graphics. Although dBASE IV lacks a field data type for graphics, third-party software firms publish applications that enable you to store graphic images in dBASE memo fields. Various add-on applications enable CA-Clipper programmers to display and edit graphic images. The images usually are in individual files, but a few third-party applications place images in memo files. CA-dbFast, for example, can display—but not edit—images stored in Windows bitmap (.bmp) files. CA-dbFast doesn't add a graphics field type to store bitmapped data within tables.

When you try to import or link Paradox 4.x, Paradox for Windows, or dBASE .dbt files that contain images or other binary data, you may receive an error message that the memo file is corrupted or that you cannot 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 graphic or binary fields simply disappear from the table.

If a dialog appears that reports a problem during importing or linking a file, the linking or importing process is canceled.

The following recommendations can help you deal with graphic images processed with other RDBMSs and add-on applications:

• Use add-on applications for xBase clones and compilers that operate with the original graphics files in their native format, such as .tif, .pcx, .gif, or .tga. In nearly all cases, the original graphics file is on your computer's fixed disk or on a file server. You can link or embed the graphics file in an Access OLE Object field by using the techniques described in Chapter 19, "Adding Graphics to Forms and Reports."

• Do not use add-on applications that incorporate graphics in .dbt files. If you are committed to this approach, use the following steps to place the offending memo file in a new file.

• If you use Paradox 4.x or Paradox for Windows with application development in Access, maintain files with graphics fields (as well as any OLE fields in Paradox for Windows tables) separate from files containing conventional data types.

• Use an OLE server that can process the graphics file type of the original image. Windows Paint is limited to Windows bitmap files (.bmp and .dib) and can only read—not save—.pcx files. To display the image in a form or report, you can create a reduced-size, 16-color or 256-color, Windows bitmap file to be displayed as a bound object. Chapter 19 discusses methods of handling images in this way.

To link or import an xBase file that contains a memo field or a Paradox file that contains graphics fields, you must be familiar with file-restructuring methods for dBASE or Paradox. To restructure an xBase file with a memo file that contains graphic images, follow these steps:

Converting Field Data Types to Access 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 quite successful in this conversion because it offers a greater variety of data types than most of the other widely used PC RDBMSs. Table 7.1 shows the correspondence of field data types between dBASE, Paradox, and Access files.

^[*] Sometimes two types of field data, separated by commas, are shown within a single column in Table 7.1. When Access exports a table that contains a data type that corresponds with one of the two field data types, the first of the two data types is assigned to the field in the exported table. The Float data type is available only in dBASE IV and 5.

If you are 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 4, "Working with Access Databases and Tables." Remember that you cannot 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 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.

◂◂See Choosing Field Data Types, Sizes, and Formats

◂◂See Adding Indexes to Tables

Setting Primary Keys and Using Index Files

Methods of setting primary keys and creating indexes differ according to the type of file you use to link a table. Tables based on Paradox and client/server RDBMS files usually have predefined primary-key fields and are indexed on the key fields. Files based on dBASE structures, however, don't have fields specified as primary-key fields and use separate index files. The following sections discuss the effects these differences have on the tables you create.

Establishing Key Fields in Linked Paradox Tables. When you link or import a Paradox or Btrieve table that has a primary-key index, Access establishes this primary key as the primary key for the new table. To verify that Access establishes a primary key for a linked Paradox table, click the Design View toolbar button. A message box states that you cannot modify some properties of the table and asks if you want to open the table anyway (see Figure 7.8). Click Yes to open the table in Design view. The primary key of the linked table is shown with the key icon next to the field name, as with primary keys for Access tables you create yourself.

When you link a Paradox .db table file that has a primary key, Access uses the Paradox .px file to establish the primary key. If you modify the values in a key field, the .db and .px files simultaneously reflect this modification.

Linking dBASE Index Files. Key-field indexing is not automatic with dBASE files linked as tables; dBASE file headers do not include data about the indexes used by the application that has the .dbf file. The first time you link the .dbf file as an Access table, you must manually link the index files associated with a dBASE file. Then, when you open the Access database with the linked table again, Access links the indexes you specify.

When you link a dBASE file as a table in your database and select the file name for the table, Access displays a Select Index Files dialog (see Figure 7.9). When you import a dBASE file, this dialog doesn't appear.

If you select a dBASE III source file, Access supplies the default .ndx file extension in the Select Index Files dialog. Your dBASE III file may have one or more .ndx index files associated with it. The five indexes that appear in Figure 7.9 are for the Customer.dbf table (a dBASE III file, created by exporting the Customers table from the Northwind.mdb sample database).

dBASE IV and 5 can create a multiple-index (.mdx) file that includes all the indexes associated with the .dbf file. The .mdx file usually has the same file name as the .dbf file. Access supplies both .mdx and .ndx as default index-file extensions when you select a dBASE IV or 5 file. The Select Index Files dialog appears whenever you select an xBase file type for linking or importing into an Access database, as described earlier in the section "Linking and Importing External Tables."

To link index files to tables created from dBASE files, follow these steps:

1.	From the file list in the Select Index Files dialog, select the index file name and click Select. Access adds the index and displays a message dialog, confirming that the index was added (see Figure 7.10). Caution Access does not test to determine whether the table's .ndx or .mdx index file matches the structure of its associated Access .dbf file. If the index file doesn't match the Access file, Access doesn't update the index file. Unfortunately, Access does not advise you that it is ignoring the non-conforming index. If you add records to the table in Access and then attempt to use the table with an xBase application that uses the proper index, you receive the error message Index does not match database. You then must reindex the .dbf file. Figure 7.10. Confirming the addition of a dBASE index to a linked table. Caution When you link a dBASE file that is used by another application, you must select all indexes associated with this file if your Access application modifies fields in the index expressions of each index file. If you do not update all the associated index files while updating the .dbf file with Access, the other application may display the error message Index does not match file. Worse, the application may produce erroneous or unpredictable results. If the message or errors occur, you must reindex the file in the other application.
2.	If more than one index file is associated with the dBASE .dbf file, you must repeat step 1 for each .ndx file until you add the names of all the indexes needed for your Access table. Then click Close in the Select Index Files dialog. Access displays the Select Unique Record Identifier dialog (see Figure 7.11). Figure 7.11. The dialog for selecting a unique index for a linked dBASE file.
3.	If you want Access to use one of the linked dBASE indexes to ensure that records have a unique value in the indexed field (that is, to use the dBASE index as a primary key in the table), double-click this index's name in the Indexes list of the Select Unique Record Identifier dialog.
4.	Click the Close button in the Select File dialog.
5.	Click the Design button in the Database window to display the structure of your linked dBASE table.
6.	If the Indexes window is not visible, click the Indexes toolbar button or choose View, Indexes. The field names that are the basis for any linked single- or multiple-field indexes appear in the Index Name text boxes of the Indexes window (see Figure 7.12).

If you try to modify the primary-key field or change the data type or the Field Size property of a linked table, and then click the Datasheet button in run mode, Access displays a dialog (see Figure 7.13). Click Yes. (Clicking No restores the original values.) Access doesn't change the properties in the linked file, nor do the Design and Datasheet windows display the changes.

Creating Access .inf Files for dBASE Indexes. When you link one or more indexes to a table created from a dBASE file, Access creates a file with the same file name as the .dbf file but with an .inf extension. The Filename.inf file is a text file that contains the path and the file name of the index you linked, in the following format (the following .inf file is for the Customer dBASE III indexes shown in Figure 7.12 and shows UNC file names to a network location):

     [dBase III]
     NDX1=\ZeuszeusdDOCTESTDATAdbase3CITY.NDX
     NDX2=\ZeuszeusdDOCTESTDATAdbase3COMPANYN.NDX
     NDX3=\ZeuszeusdDOCTESTDATAdbase3POSTALCO.NDX
     NDX4=\ZeuszeusdDOCTESTDATAdbase3PRIMARYK.NDX
     NDX5=\ZeuszeusdDOCTESTDATAdbase3REGION.NDX

     [UIDX1 PRIMARYK]
     NDX5=\ZeuszeusdDOCTESTDATAdbase3REGION.NDX

The .inf file is located in the same folder as the .dbf file you are linking. If you create an invalid .inf file or if one or more of the indexes listed in the .inf file isn't found in the specified location, Access terminates the current link operation, and you must link the dBASE file again. Figure 7.14 shows the error message you see if Access can't find an index specified in the .inf file. When you relink the dBASE file to correct a prior error, a message box indicates that an .inf file already exists (see Figure 7.15). If the indexes changed or you moved the indexes, click Yes to create a new .inf file.

Linking Tables from Other Access Databases

The procedure for linking a table from one Access database to another Access database is like the procedure for linking other tables. To link a table from Northwind.mdb to your test database, for example, follow these steps:

Access maintains a record of the drive and folder containing the files responsible for your linked tables. If you rename or change the location of a file that you linked as a table, Access no longer can find the file and displays an error dialog (see Figure 7.17).

Using the Linked Table Manager Add-In to Relink Tables

Before Access 2.0, if you moved a file that was linked to or contained objects linked to an Access database, you had to delete the linked tables and then relink the tables from their new location. Access 97 provides an add-in assistant known as the Linked Table Manager to simplify relinking tables. (The Linked Table Manager was known as the Attachment Manager in Access 2.0.)

If you move an Access, dBASE, FoxPro, or Paradox file that provides a table linked to an Access 97 database, choose Tools, Add-Ins, Linked Table Manager. The Linked Table Manager's window lists all the linked tables (except linked ODBC tables). Access also displays the path to the database containing the linked table(s) at the time the link was created. (You also can view the path to the database containing a linked table by opening the linked table in Design view and opening the Table Properties window.) Click the check box of the file(s) whose location(s) changed (see Figure 7.18).

Click OK to display the Select New Location of the TableName dialog shown in Figure 7.19. Select the folder and file where the table is located, and then click Open to change the 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.

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 share the file with other users who are allowed to update the file, or you make updates of the file available to other users.

• You use another RDBMS to modify the file in any way.

• The 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 are developing an application and want to use data types or Field Size properties different from those chosen for you by Access.

• You or the users of your application do not have online access to the required database files and cannot link them.

• You want to use a key field different from the field specified in a Paradox 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 22, "Exploring Relational Database Design and Implementation."

• You need Access to allow duplicate values in your table when a primary-key field precludes duplicate values.

If you decide to use a temporarily imported table in an application that, when completed, also will use a linked table, make sure that you do not change any field names, field data types, or Field Size properties after you import the table. If you change Field Name properties, you may have to make many changes to forms, reports, macros, and Access VBA code when you change to a linked table. If your application involves Paradox and client/server database tables, do not change the primary-key fields of these tables. With dBASE tables, make sure that the indexes you create correspond to the indexes of the associated .ndx or .mdx files.