An Approach to Rapid Deployment of MS Access Connected via ODBC to Oracle

 by Bruce Knox  bknox @t uaex.edu  
   Oracle Database Programmer/Analyst                                                         date last updated 02/13/2014
  

 

 

             note: the BannerAccess page and Summit Links are found at http://betwinx.com/BannerAccess.htm 

 

This is an approach to rapid deployment of MS Access connected via ODBC to Oracle as an ad hoc reporting tool for SCT's Banner.   The objective here is to have the Banner tables and views immediately available and quickly useful to a MS Access user. 

We have identified three successful approaches to using MS Access with Banner from actual sites that are currently using MS Access with Banner.  All three approaches are a part of our implementation.  These are:  

1. Use of a Data Warehouse (Server) separate from the Banner Server.  
2. Use of O:A ,or Object Access Views, which is a part of the base Banner product.  
3. Use of SQL Pass-Through Queries to improve response times.

We are providing predefined MS Access databases for each of our business office functional areas. These provide the new MS Access users with a head start in using this tool with Banner.  Frequently used views and tables from our prior work (Focus and SQL*Plus) plus the O:A Views are provided as a standard set of pre-written SQL Pass-Through Queries.

These queries have all standard WHERE clause logical conditions (qualifiers) included. For example, since we always require IDtable_CHANGE_IND IS NULL, this is standard for the query instead of requiring the user to re-specify this code for each use of IDtable (Person Identification/Name Repeating Table).  This is even more helpful with tables that have effective dates or termination dates for records.  If needed, all records can still be queried, but this will be very unusual.  Note: IDtable has been substituted for the actual Banner table name for this public web page. 

Special versions of the IDtable query for People, Vendors, and Organizations plus a Names query which includes all of the common employee name combinations and formats are included. This will speed the creation of user written queries.

The objective here is to have the Banner tables and views immediately available and quickly useful to a MS Access user.   While you can have the table columns available simply by coding a SELECT * FROM table_name, you will find that most users find the qualifiers required for using the Banner tables a daunting barrier to use.  Dates are an immediate display problem due to some date values containing time values.  For the purposes of reporting using MS Access, I have found no need for the time included in the Oracle date fields to be passed to MS Access providing that the SQL qualifiers select the current records in the SQL Pass-Through code.

The SQL*Plus script table_to_query.sql can be used to quickly create the code needed for the MS Access query.  The script strips the time values from all dates and presents the original Banner column names for all fields.   My table_to_query.sql script includes no Banner table or view name references.  The generated code will include suggested WHERE statements, an ORDER BY statement, and suggestions for setting Relationships in the MS Access database.

 

There are a few quirks moving code from SQL*Plus to MS Access SQL Pass-Though.  The code in the SQL Pass-Through is SQL not SQL*Plus.  So many of the statements that you depend upon frequently in SQL*Plus programming are not available.  These Oracle extensions to SQL include DEFINE, SPOOL, SET, DESCRIBE, HOST, COLUMN and many others.  Most missed of all from the SQL Pass-Through is SQL*Plus' COLUMN.  It takes considerable rethinking to code without these powerful SQL*Plus commands.

 

Nevertheless, the MS Access queries work just fine without SQL*Plus.  The ad hoc user certainly will not miss the SQL*Plus constructs.  Number formatting is very limited with SQL Pass-Through code alone.  MS Access does offer extensive control over the appearance of columns.  You must alter the column properties of numeric fields in the Queries that use the SQL Pass-Through  as components to correctly format and decimal align the numeric fields.

 

To effectively use this approach, you will need a logon Form.  I have based mine upon code from the University of Regina demo given at SCT Summit 2000 (thanks again!).  You need this type of logon Form to avoid exposing your Banner password in the MS Access code while avoiding a logon for each use of each table.  

 

An example MS Access database is available at http://betwinx.com/ms_access_logon.htm that includes the logon form and a generalized connect for all SQL Pass-Through Queries.  The Logon form is completely generalize and no longer references any Banner table in the included code.

 

In addition to using the logon Form and the table_to_query.sql script, there are some additional time saving steps for the setup process:

Using the Template Query, you can avoid several steps in setting up queries by simply copying the Template query and substituting the new Oracle SQL code for the old.  The copy process automatically copies the original query's ODBC connect information for you.  There is no need to add this new query to the logon Form event code, it will be connected by the generalized code in the logon Form.

 

I  used this approach to set up a skeleton database that can be used by users new to MS Access.  When the user opens a copy of the skeleton database, the key Banner tables are presented as completed queries and examples.  This allows the user to have some positive programming experiences early into learning MS Access.  

 

You could also preset some MS Access Relationships, but I now recommend against doing so.  Opening the Relationships window with a number of relationships set is a very time and capacity consuming event.  Using the MS Access Relationships will be severely limited by the capacity of the least client PC.  Note that the SQL Pass-Through code generated by table_to_query.sql  includes hints for setting the relationships and can be done quickly in the New Query Design mode.

 

The power user can quickly adapt the skeleton to their own use and additional Banner tables can be added quickly as needed.  User created queries that are found to be needed by others can be imported into the skeleton database and a programmer can optimize the new query for all users.

 

Read Only?

Using the SQL Pass-Through Query and Visual Basic Access to Connect to Oracle gives a good solution to the problem of how to make MS Access appear to be Read-Only for a trusted user.   By agreeing to prohibit the use of Linked Tables (MS Access Menus: File>Get External Data>Link Tables), you can avoid the possibility of an inadvertent update due to someone changing a value in a displayed MS Access query of an Oracle table.

 

While the Oracle tables are actually linked by the Logon code's connect logic, this will not be apparent through the user interface.  To the application user, the MS Access database will appear to be an Oracle user interface. 

 

This is a pragmatic solution that gives security comparable to that of giving SQL*Plus access to a user.  While technically the SQL Pass-Through Query can be used to insert, delete, or update records in the Oracle database, any SQL Pass-Through Query that returns records is by design Read-Only.  One must deliberately code the SQL INSERT, DELETE, or UPDATE statements and change a default property of the MS Access Query to do anything but read the database.

Of course, you also have the ability to make the user's access read-only via the Oracle privilegesAnd with Oracle8 (Net8) you can also setup the PC's ODBC link as Read-Only.

Also see  Installing Oracle8 ODBC on Windows 2000. 

Disclaimer

 

Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

 

http://betwinx.com is my personal site.  betwinx?  This site is largely oriented to Oracle Reporting Solutions in the context of generating bespoken reports from Ellucian's Banner product. 
(I maintain a number of special topic pages with links from this primary site.)                                 
View Bruce Knox's profile on LinkedIn