MS Access Pass Through Queries

1
MS AccessPass Through Queries
Presenter Dan DeBower Technical
Consultant Systems Computer Technology
Corp. ddebower_at_sctcorp.com Tuesday, April 10th
-- 1000am
2
Session Rules of Etiquette

• Please turn off your cell phone/beeper
• If you must leave the session early, please do so
  as discreetly as possible
• Please avoid side conversation during the
  presentation
• Thank you for your cooperation!

3
What youll learn

• After this session you will be able to
• Connect Microsoft Access to SCT Banner via ODBC
  (Open Database Connectivity)
• Create a simple Pass-Through Query
• Increase the efficiency of your queries and
  reports

4
Topics

• ODBC Open Database Connectivity
• Installing the ODBC driver
• Establish a DSN (Data Source Name)
• Pass-Through Queries
• WHY Pass-Through Queries?
• The Make-Table Query and Pass-Throughs
• Questions?

5
ODBC

• Open Database Connectivity

6
ODBC

• Open Database Connectivity
• A standard application programming interface (or
  API) for accessing a wide range of databases
• Connects MS Access to an ODBC capable database
  (Specifically, Oracle)
• Originally released in 1992 by the SQL Access
  Group

7
ODBC Drivers

• The center of an ODBC connection
• Translates requests by an application into
  commands usable by the host database
• Utilizes ODBC defined Functions, Error Codes, and
  Data Types that are database independent
• Available from many vendors -- including Oracle,
  Microsoft, InterBase, OpenLink, Merant
  (Intersolv), Simba, and others

8
The ODBC Stack
9
Choosing an ODBC Driver

• Oracle and Microsoft offer ODBC drivers for
  Oracle databases with no license fees
• Suggested driver Oracle
• The Oracle and Microsoft drivers are more than
  sufficient for MS Access database linking and
  Pass-Through Queries
• Consider other vendors if you are developing
  applications that use ODBC directly

10
Installing an ODBC Driver

• The Oracle ODBC driver requires Oracles SQLNet
• The TNSNames.ora file must be available to your
  workstation
• If you can connect SQL Plus to your target
  database, then SQLNet is probably set up
  correctly

11
Installing an ODBC Driver

• Download an appropriate driver fromhttp//technet
  .oracle.com/software/download.htm

• Choose the driver that best matches your version
  of Oracle
• An exact match isnt necessary, but you should
  match major releases (i.e. 7 or 8)

12
Installing an ODBC Driver

• Have SQLPlus installed on your workstation before
  installing the Oracle ODBC driver
• Allows testing of SQLNet or Net8
• Valuable during Pass-Through development
• The Oracle 8 driver REQUIRES that the Oracle
  Universal Installer be previously installed on
  your workstation

13
Installing an ODBC Driver

• Install your driver
• Oracle 7
• Execute the downloaded file and start the
  included Oracle installer
• Oracle 8
• Execute the downloaded file and start the Oracle
  Universal Installer

14
Create a DSN (Data Source Name)

• Open ODBC Data Sources on the Windows Control
  Panel
• Select the System DSN tab
• Click Add...
• Select your driver from the list
• Configure your DSN

15
Configure your DSN

• Select a DSN name (like Banner)
• DSN names may be standardized at your site, so be
  sure to request guidance from your IT department
  or Computer Center

• Leave the other fields blank, theyll be
  specified in your queries

16
Test your ODBC Connection

• Oracle includes a test program in their ODBC
  drivers called32-bit ODBC TEST

• Execute the test program, connect to your
  database, and enter a simple query
• If you have SQLPlus installed, test your
  connection there too!

17
A note about security

• ODBC is JUST AS SECURE as a connection through
  SQLPlus or another SQL editor
• Access to tables and other objects are granted to
  SQL and ODBC connections in the same way
• All (legitimate) access to Oracle is through
  SQLNet or Net8 -- and they maintain security,
  not the ODBC driver

18
Pass-Through Queries
19
Create a Query

• The NEW button, on the database Queries tab.
• Or from the menu Insert - Query
• From the wizard, select Design View
• And Close the show table window
• And Query - SQL Specific - Pass-Through

20
Prepare your query

• Create an ODBC Connection String
• In the Properties window enter a connection
  string
• ODBCDSN????DBQ????UID????PWD????
• DSN - your ODBC Data Source Name
• DBQ - your Oracle database instance
• UID - your Oracle UserID
• PWD - your Oracle Password (Security???)

21
Prepare your query

• Create an ODBC Connection String
• If you leave out the Username and Password,
  Access will display a connection window
• ODBCDSNBannerDBQPROD

22
Prepare your query

• Enter your SQL

• Remember - you can write and test your SQL
  queries in SQLPlus and then Copy-Paste from the
  SQL editor to the Pass-Through window!

23
Why Pass-Through Queries?

• Because you want to
• Because you need to
• Because you have to!

24
Why - Because you want to

• Pass-Throughs can increase the efficiency of your
  queries
• The SQL is passed directly to Oracle, Access
  doesnt process the query
• SO Oracle SQL efficiency techniques can be
  applied to your Pass-Through!

25
Efficiency

• Include only the tables and where conditions that
  you absolutely must have
• Extra tables and wheres take time to process
• Order your where statements
• Put the most restrictive rules LAST
• Why? Oracle evaluates where statements from
  bottom to top!

26
Why - Because you need to

• Pass-Throughs arent processed by Access, so
  theyre not limited by Access!
• For instance, you can use Oracle Functions and
  Procedures in a Pass-Through that you couldnt
  use elsewhere in Access
• HINT If your Pass-Through Function or Procedure
  doesnt work - enclose it in curly braces so
  Oracle can identify it

27
Why - Because you have to!

• The Microsoft Jet Database Engine that lies
  behind Access doesnt respond well to Oracle
  databases with very large numbers of objects
• ODBC Table Linking -- the alternative to
  Pass-Throughs -- downloads data about all
  available Oracle objects, and that takes time
• The Linking process can take so long that it
  times out! You cant use linking at all!

28
Make-Table Queries

• Using Access Make-Table Queries can simplify your
  use of Pass-Throughs
• A Make-Table will execute your query and store
  the results in a permanent Access table
• If you didnt hard-code your password, youll be
  asked for it only once -- when you Make-Table
• Your Pass-Through wont run unnecessarily!

29
Make-Table Queries

• First, create and test your Pass-Through

• Create a new query in Design View
• From the menu Query - Make-Table Query
• Select your query
• Double click to include all your fields

30
An alternative to ODBC

• Oracle Objects for OLE (or Oracle Glue)
• Utilizes Microsoft OLE (Object Linking and
  Embedding), a set of APIs that produce compound,
  multipart documents
• Only available between Microsoft Visual Basic
  applications (Access, VB) and Oracle
• Strictly a programming interface (via Visual
  Basic)

31
Summary

• ODBC is much, much more than Table-Linking and
  Pass-Through Queries
• There are many reasons for using Pass-Throughs
  Efficiency, flexibility, necessity
• An understanding of SQL will give you more tools
  to use your data, and give you a better
  understanding of your database!

32
ExamplesAndQuestions?

• Dont forget your evaluations!