ACC: How to Use an Import/Export Spec. in Another Database

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

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how to use a previously defined import/export specification from one database in another database. Note that the method described in this article involves the manipulation of system tables. While 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.

MORE INFORMATION

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, delete the existing MSysIMEXSpecs and MSysIMEXColumns tables in the new database, and then import those tables from the other database.

Importing an Import/Export Specification into a New Database

The following examples demonstrate how to import an import/export specification into a new database:

In Microsoft Access 7.0 and 97:

1. Open the database containing the import/export specification you want to import into another database.

2. On the Tools menu, click Options. Click the View tab. In the Show panel, click to select the System Objects check box, and click OK.

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

   NOTE: In Microsoft Access 7.0, the new database will include MSysIMEXColumns and MSysIMEXSpecs. Delete these two system tables.

4. On the File menu, click Get External Data, and then click Import.

5. On the Import screen, click the Microsoft Access database with the desired import/export specifications, and then click Import.

6. On the Import Objects screen, click the Tables tab. Click the MsysIMEXSpecs and MSysIMEXColumns tables, and then click OK.

7. On the Tools menu, click Options. Click the View tab. In the Show panel, click to clear the System Objects check box, and click OK.

In Microsoft Access 1.x and 2.0:

1. Open the database containing the import/export specification you want to import into another database.

2. On the View menu, click Options. In the Category box, select General, and then set the Show System Objects item to Yes. Click OK.

3. On the File menu, click New Database. Type a name for the new database in the File Name box; then click OK.

4. On the File menu, click Import.

5. In the Data Source box, select Microsoft Access, and then click OK.

6. In the File Name box, select the database that contains the import/ export specification you want to import, and then click OK.

7. In the Object Type box, select Tables. In the Objects In <Database> box, select MsysIMEXSpecs and then click the Import button. Click OK when you are notified that the table was successfully imported.

8. Repeat step 7 using the MsysIMEXColumns table, and then click Close.

9. On the View menu, click Options. In the Category box, select General, and then set the Show System Objects item to No. Click OK.

REFERENCES