MS Access Pass Through Queries - PowerPoint PPT Presentation

About This Presentation
Title:

MS Access Pass Through Queries

Description:

Connect Microsoft Access to SCT Banner via ODBC (Open Database Connectivity) ... Only available between Microsoft Visual Basic applications (Access, VB) and Oracle ... – PowerPoint PPT presentation

Number of Views:82
Avg rating:3.0/5.0
Slides: 33
Provided by: bets164
Category:

less

Transcript and Presenter's Notes

Title: 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!
Write a Comment
User Comments (0)
About PowerShow.com