Using the SQL query pass-through option in Voyager PrePackaged Access Reports

Question

How can I create a Voyager PrePackaged Access Reports SQL query that uses the pass-through option?

Answer

• Microsoft Access natively supports a limited subset of SQL. 
• Another option for running a SQL query in Access is to make it a pass-through query.
• SQL pass-through queries are used to send commands directly to the Voyager Oracle database.
• By using an SQL pass-through query, you work directly with the Voyager tables instead of having the Microsoft Access database process the query code.
• On the Voyager customer listserv, sometimes someone will post SQL code that you want to use, but which they wrote for use via this "direct" option.  If you try to use the code in the normal way by having Access "interpret" it, it will produce errors.
• Note that in a pass-through query there is no "Design View" in Access. There is only the "SQL View."

Here are the steps for making a pass-through query in Access.  These steps assume you are working on a PC that already has PrePackaged Access reports functioning properly.

1. On the Create tab, click Query Design in the Other group.
2. Click Close in the Show Table dialog box without adding any tables or queries
3. On the Design tab, click Pass-Through in the Query Type workgroup.
4. In the query Property Sheet, place the mouse pointer in the ODBC Connect Str property, and then, click the Build (...) button.
5. The Select Data Source window will open.
6. In the Select Data Source > Machine Data Source tab click on the VGER Data Source (if you do not see the VGER Data Source, you do not have the Oracle Client installed correctly.  See: Installation and Configuration of ODBC for Voyager.)
7. Double-click on the VGER entry and enter your "ro_xxxdb" password.
8. When you are prompted to save the password in the connection string, click Yes if you want the password and logon name to be stored with the connection string information (recommended).
9. In the SQL Pass-Through Query window, copy/paste your pass-through SQL query.
10. Click Run in the Results group on the Design tab, or click Datasheet View on the Status Bar.

Example (keep in mind Access changes over time and this may look different in your version of the software):

Additional Information

This is a very simple one-line SQL query you can use to test your pass-through query (it counts the number of charges):

  select count(*) from circ_transactions;

For more help see the Microsoft instructions for pass-through queries.

See also: What is the difference between Voyage Prepackaged Access Reports SQL and sqlplus (SQL*Plus) on the server?

• Article last edited: 24-May-2019