Transcript No Slide Title

S
MS Access
Pass Through Queries
the
power
of partnership
Presenter:
Dan DeBower
Technical Consultant
Systems & Computer Technology
Corp.
[email protected]
Tuesday, April 10th -- 10:00am
Evaluation Code
S
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!
2
Evaluation Code
S
What you’ll 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
3
Evaluation Code
S
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?
4
Evaluation Code
S
ODBC
the
power
of partnership
Open Database Connectivity
Evaluation Code
S
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
6
Evaluation Code
S
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
7
• Available from many vendors -- including
Oracle, Microsoft, InterBase, OpenLink,
Merant (Intersolv), Simba, and others
Evaluation Code
S
The ODBC Stack
ODBC Application
(Access, Excel, Crystal Reports)
Driver Manager
(ODBC.dll)
8
ODBC Driver #1
Oracle
ODBC Driver #2
DB2
ODBC Driver #3
dBase
Database Transport
SQL*Net or Net8
Database Transport
Database Transport
Network Transport
TCP/IP
Network Transport
Network Transport
Data Source
Oracle
Data Source
DB2
Data Source
dBase
Evaluation Code
S
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
9
• Consider other vendors if you are
developing applications that use ODBC
directly
Evaluation Code
S
Installing an ODBC Driver
• The Oracle ODBC driver requires Oracle’s
SQL*Net
• The TNSNames.ora file must be available
to your workstation
• If you can connect SQL Plus to your target
database, then SQL*Net is probably set up
correctly
10
Evaluation Code
S
Installing an ODBC Driver
• Download an appropriate driver from
http://technet.oracle.com/software/download.
htm
• Choose the driver that best matches your
version of Oracle
• An exact match isn’t necessary, but you
should match major releases (i.e. 7 or 8)
11
Evaluation Code
S
Installing an ODBC Driver
• Have SQLPlus installed on your
workstation before installing the Oracle
ODBC driver
• Allows testing of SQL*Net or Net8
• Valuable during Pass-Through
development
12
• The Oracle 8 driver REQUIRES that the
Oracle Universal Installer be previously
installed on your workstation
Evaluation Code
S
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
13
Evaluation Code
S
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
14
Evaluation Code
S
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, they’ll
be specified in
your queries
15
Evaluation Code
S
Test your ODBC Connection
• Oracle includes a test program
in their ODBC drivers called
32-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!
16
Evaluation Code
S
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
SQL*Net or Net8 -- and they maintain
security, not the ODBC driver
17
Evaluation Code
S
Pass-Through
Queries
the
power
of partnership
Evaluation Code
S
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
19
Evaluation Code
S
Prepare your query
• Create an ODBC Connection String
• In the Properties window enter a
connection string:
ODBC;DSN=????;DBQ=????;UID=????;PWD=????;
•
•
•
•
20
DSN
DBQ
UID
PWD
-
your ODBC Data Source Name
your Oracle database instance
your Oracle UserID
your Oracle Password (Security???)
Evaluation Code
S
Prepare your query
• Create an ODBC Connection String
• If you leave out the Username and
Password, Access will display a
connection window
ODBC;DSN=Banner;DBQ=PROD;
21
Evaluation Code
S
Prepare your query
• Enter your SQL
22
• Remember - you can write and test your
SQL queries in SQLPlus and then CopyPaste from the SQL editor to the PassThrough window!
Evaluation Code
S
Why Pass-Through Queries?
• Because you want to
• Because you need to
• Because you have to!
23
Evaluation Code
S
Why - Because you want to
• Pass-Throughs can increase the efficiency
of your queries
• The SQL is passed directly to Oracle,
Access doesn’t process the query
• SO Oracle SQL efficiency techniques can
be applied to your Pass-Through!
24
Evaluation Code
S
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!
25
Evaluation Code
S
Why - Because you need to
• Pass-Throughs aren’t processed by
Access, so they’re not limited by Access!
• For instance, you can use Oracle
Functions and Procedures in a PassThrough that you couldn’t use elsewhere
in Access
• HINT: If your Pass-Through Function or
Procedure doesn’t work - enclose it in
curly braces { } so Oracle can identify it
26
Evaluation Code
S
Why - Because you have to!
• The Microsoft Jet Database Engine that
lies behind Access doesn’t 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
27
Evaluation Code
S
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 didn’t hard-code your password,
you’ll be asked for it only once -- when
you Make-Table
28
• Your Pass-Through won’t run
Evaluation Code
S
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
29
Evaluation Code
S
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
30
• Strictly a programming interfaceEvaluation
(via Code
S
Summary
• ODBC is much, much more than TableLinking and Pass-Through Queries
• There are many reasons for using PassThroughs: 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!
31
Evaluation Code
S
the
power
of partnership
Examples
And
Question
s?
Don’t forget your evaluations!
Evaluation Code