ACC: Export Table to Other Database Type Does Not Create Index

• Microsoft Access 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you export a Microsoft Access 7.0 or 97 table with a primary key field to any of the dBASE or Microsoft FoxPro formats, an index is created. This does not happen in Microsoft Access 1.x and 2.0. Exporting to a Paradox format does not create an index in any version of Microsoft Access.

In Microsoft Access 1.x and 2.0, if you import a supported foreign database table having an index or primary key, the resulting Microsoft Access table does not have a primary key defined.

However, in Microsoft Access 7.0 and 97, there is an option for field name prefixes or suffixes to indicate which field names should be indexed automatically on import. The default strings are: ID, key, code, and num. Memo and OLE Object fields are not indexed.

CAUSE

In Microsoft Access 1.x and 2.0, the import and export utility imports and exports only data, not the key or index definitions.

RESOLUTION

When you export a Microsoft Access table to an external database format, you must use the native application for that file type to define the index or key fields. Likewise, in versions 1.x and 2.0 after you import a foreign database into your Microsoft Access database, you must open the table in Design view and define the primary key field or fields. You must do this in Microsoft Access 7.0 and 97 only if the AutoIndex option found no matches.

STATUS

This behavior is by design.

MORE INFORMATION

The following database formats can be imported or exported by Microsoft Access. After a table is exported from Microsoft Access, use the native product for each file type to create indexes or key fields.

   FoxPro 2.x
   Visual FoxPro 3.0 (Microsoft Access 7.0 and 97 only)
   Paradox 3.x
   Paradox 4.x (Microsoft Access 2.0, 7.0, and 97 only)
   Paradox 5.0 (Microsoft Access 7.0 and 97 only)
   dBase III
   dBASE III +
   dBASE IV
   dBASE for Windows version 5.0 (Microsoft Access 7.0 and 97 only)
   Btrieve (Microsoft Access 2.0 or earlier only)
   SQL Server

REFERENCES

For more information about the AutoIndex Option, search the Help Index for "AutoIndex."