MS Access Pass Through Queries

1. MS AccessPass Through Queries Presenter: Dan DeBower Technical Consultant Systems & Computer Technology Corp. ddebower@sctcorp.com Tuesday, April 10th -- 10:00am

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 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

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. Open Database Connectivity ODBC

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 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

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 isn’t 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 SQL*Net 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, they’ll 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 SQL*Net 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: ODBC;DSN=????;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 ODBC;DSN=Banner;DBQ=PROD;

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 doesn’t 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 aren’t processed by Access, so they’re not limited by Access! • For instance, you can use Oracle Functions and Procedures in a Pass-Through 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

27. 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 it times out! You can’t 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 didn’t hard-code your password, you’ll be asked for it only once -- when you Make-Table • Your Pass-Through won’t 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. Don’t forget your evaluations! ExamplesAndQuestions?