Using Microsoft Access with Epicor (formerly Activant) Prophet 21 databases

The Prophet 21 database structure includes DYN, GL, STAT, GATE, and P21 tables.

The DYN, GL, and STAT tables are standard Progress database tables, and may be queried normally by any tool connecting through OpenLink data access drivers.

The P21 and GATE tables, however, are C-ISAM tables, which are inaccessible to many tools, including the Microsoft Access table-linking and query-building wizards. Fortunately, there are other ways to work with the data in the P21 tables, even with Microsoft Access.

Microsoft Access includes "Pass-Through Query" functionality. This lets you provide Microsoft Access with queries which will be passed directly to the back-end database.

You can manually construct such queries, if you're comfortable doing so. You can also use tools such as Microsoft Excel and Microsoft Query, which are both able to work with P21 tables, to graphically build and test queries. Once built, the text of these queries can be copied from the SQL view, and used with Microsoft Access or other tools which cannot work more dynamically with the C-ISAM tables.

The following steps will let you execute a "Pass-Through" query with Microsoft Access --

1. Click to the Queries tab of the database dialog box.
   
2. Click on New.
   
3. Select Design View.
   
4. Close the dialog box.
   
5. In the Access menu bar, select Query -> SQL Specific -> Pass Through.
   
6. Type or paste in your query.
   
7. Click on the exclamation point (!) to run the query.
   
8. Select the ODBC DSN which targets the P21 database.
   
9. Provide authentication & click Connect.