ACC2000: How to Copy an Import/Export Specification from Another Database

This article shows you how to use a previously defined import/export specification from one database in another database.

NOTE: The method described in this article involves the manipulation of system tables. Although manipulating system tables is neither supported nor encouraged, the only method for using a previously defined import/export specification in another database is to transfer the relevant system tables from one database to another.

Import/export specifications are stored in the system tables MSysIMEXSpecs and MSysIMEXColumns. These two tables can be imported or exported, but the target database must not contain an existing import/export specification. If the target database already contains an import/export specification, additional tables that Microsoft Access does not recognize will be created.

The best approach is to create a new database for the import/export specification and then import those tables from the other database.

Creating an Import/Export Specification

To create an Import/Export Specification in the sample database Northwind.mdb, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. On the Tools menu, click Options, on the View tab, click to select the System Objects check box if it is not already selected, and then click OK.
3. In the Database window, click Tables and notice the appearance of the tables MSysIMEXSpecs and MSysIMEXColumns, along with other system tables identified by the characters "MSys" in their names.
4. Click the MSysIMEXSpecs table, click Open to view its records (there may be none), and then close the table.
5. Click the Customers table, and then on the File menu, click Export.
6. In the Export Table 'Customers' As dialog box, accept the file name Customers; in the Save As Type list, click Text Files (*.txt, *.csv, *.tab, *.asc), and then click Save.
7. In the Export Text Wizard dialog box, click Advanced.
8. In the Customers Export Specification dialog box, click {tab} in the Field Delimiter list, and then click Save As.
9. In the Save Import/Export Specification dialog box, click OK, and then click OK in the Customers Export Specification dialog box.
10. In the Export Text Wizard dialog box, click Next and notice that Tab is the selected delimiter; again click Next.
11. Notice the path and file name of the exported data, click Finish, and then click OK in the completion message box.
12. In the database window, open the MSysIMEXSpecs table and notice the new record.
13. On the Tools menu, click Options, and then on the View tab, click to clear System Objects.
14. Close the Northwind database.

Importing an Import/Export Specification into a New Database

To import an import/export specification into a new database, follow these steps:

1. On the File menu, click New Database, click Blank Database, and then OK. Type a name for the new database in the File Name box and click Create.
2. On the File menu, point to Get External Data, and then click Import.
3. On the Import screen, click the Microsoft Access database with the import/export specifications that you want, and then click Import.
4. On the Import Objects screen, click Options.
5. Under Import, click to clear the Relationships check box. Click to select the Import/Export Specs check box, and then click OK.

For more information about import/export specifications, click Microsoft Access Help on the Help menu, type export a datasheet in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.