%META:TOPICPARENT{name="P21ConfigurationLinks"}%

= 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 <nop>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 --

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