Skip Headers
Oracle® SQL Developer Supplementary Information for Microsoft Access Migrations
Release 3.0

E18459-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Application Performance Tuning

This chapter provides suggestions for tuning and customizing the way Oracle and Microsoft Access work together.

This chapter includes the following sections:

5.1 Selecting ODBC Drivers

There are a number of ODBC drivers available for Oracle. In addition to the driver supplied by Oracle, drivers are also available from Microsoft, Visigenic, MERANT, and others.

The performance of ODBC drivers can vary. If you are building a large-scale application, you need to profile the different ODBC drivers with the application. The best way to determine the performance is with the ODBC or OCI spy programs. These programs show you the calls that Microsoft Jet database engine makes to the ODBC API. They also show you the calls the Oracle ODBC driver makes against OCI.

5.2 Using Server-based Parameter Table

If you are administering an Oracle database that uses Microsoft Access as a front end, you can create a special parameter table in the Oracle database named MSysConf to help you control communication between the Microsoft Access application and the Oracle database. When Microsoft Access first makes a connection to an Oracle database, it checks to see if the MSysConf table is present within the Oracle user schema that you are connecting to. You can use the MSysConf table to prevent storage of user logon information within a linked table that increases the security of the application. You can also use the MSysConf table to optimize the record retrieval characteristics. The following table illustrates the structure of the MSysConf table within Oracle:

Column Data Type
Config
NUMBER(10)
chValue
VARCHAR2(255)
nValue
NUMBER(10)
Comments
VARCHAR2(255)

The following table illustrates the Config and nValue column values that you can use to customize the way Microsoft Access interfaces with Oracle:

Config nValue Description
101 0 Do not allow the user to store the USERID and PASSWORD in linked tables.
101 1 Allow the user to store the USERID and PASSWORD in linked tables (default).
102 D Delays D seconds between each retrieval.
103 N Fetches N rows for each retrieval.

It is recommended that you create an MSysConf table in each Oracle database even if you plan on using the defaults. This way you can change the values in the table, rather than remember how to create and name the table at a later time.

5.3 Improving Application Startup Performance

You can tune Microsoft Access to speed up the process of establishing an ODBC connection at application startup time. When Microsoft Access opens a connection to an ODBC database, it determines the level of functionality provided by the particular ODBC driver. If you are relying on Oracle to provide full security, you can bypass attempts by Microsoft Access to login to Oracle using the user, group, and password information of Microsoft Access.

The value of the TryJetAuth attribute determines whether login authentication is bypassed. The default value of the TryJetAuth attribute, equal to 1, causes Microsoft Access to attempt connection to the Oracle database using the Microsoft Access login information. You should set the TryJetAuth attribute equal to 0 in order to bypass the Microsoft Access login authentication. This step saves between one and two seconds when making the first connection to Oracle.

In Microsoft Access 97 and Microsoft Access 2000, you must modify the Windows registry using the Registry Editor (regedit). Table 5-1 displays the registry key location depending on the version of Microsoft Access you have installed on the system.

Table 5-1 Registry Key Location

Microsoft Access Release Registry Key Location

Microsoft Access 97

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\ODBC

Microsoft Access 2000, 2002 (XP), and 2003

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC


5.4 Speeding Up Microsoft Jet Database Engine Operations

In order to ensure that the Microsoft Access forms and reports operate against the data residing in the Oracle database, SQL Developer generates ODBC link tables in the Microsoft Access database. These ODBC link tables reference the tables in the Oracle database. Using ODBC link tables within a Microsoft Access application incurs a performance penalty. Specifically, although the connection time is faster due to caching of some ODBC data source information within the ODBC link table object, DML operations using ODBC link tables are slower.

The CurrentDB object is used extensively within Microsoft Access VBA code to reference database information. Prior to migration, the CurrentDB object references the local Microsoft Access tables. After SQL Developer has modified the Microsoft Access database, the CurrentDB object references the ODBC link tables.

In order to speed up Microsoft Jet database engine operation with ODBC data sources, it is necessary to minimize the direct use of the ODBC link tables. One way of doing this within the VBA code is to use the Jet Workspace Database object instead of the CurrentDB object. The Database object does not reference the ODBC link tables. Instead, it requires a direct connection to the Oracle tables.

The modifications required within the VBA code are minimal as both the CurrentDB object and the Database object expose the same set of properties and methods.

To replace the CurrentDB object references with Database object references:

  1. Create a global variable of type Database. For example:

Global dbOracle As Database
  1. During the initialization stage, create the Database object by creating a connection to the Oracle database as follows:

Dim dsn As String
Dim uid As String
Dim pwd As String
Dim odbcConnectStr As String

dsn = "ora817"
uid = "system"
pwd = "manager"

' build up the connect string
odbcConnectStr = "ODBC;DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd

' use Microsoft JET Workspace to make a connection to the Oracle database
Set dbOracle = DBEngine.Workspaces(0).OpenDatabase(dsn,_
dbDriverCompleteRequired, False, odbcConnectStr)
  1. Replace all references to CurrentDB with a reference to dbOracle.

    Note:

    Since the Database object references the Oracle tables directly, it is important to specify the Oracle table names instead of the Microsoft Access table names when you are performing table operations. The table names can be different between Microsoft Access and Oracle due to the different object naming restrictions imposed by both databases.

You cannot remove the ODBC link tables from Microsoft Access. This is because although the VBA code no longer references the ODBC link tables, the forms and reports within the database still directly reference these ODBC link tables. The effort involved in modifying the forms and reports so that they do not reference the ODBC link tables is considerable. It involves re-coding forms and reports as well as reprogramming all record navigation and manipulation. The Microsoft Jet database engine has not been bypassed as a result of these modifications. Because the Microsoft Jet database engine is still in use, a full table scan is still performed when retrieving records from the Oracle database. For more information about removing the Microsoft Jet database engine bottleneck, see Eliminating the Microsoft Jet Database Engine.

5.5 Eliminating the Microsoft Jet Database Engine

The principal reason for the significant performance degradation of a Microsoft Access application using an ODBC data source is the Microsoft Jet database engine. The problem with Microsoft Jet database engine is that it always performs a full table scan when a table is queried. This means that when a Microsoft Access form or report references an Oracle table through an ODBC link the Microsoft Jet database engine must retrieve the entire contents of the table into local memory before it can perform the query. Eliminating Microsoft Jet database engine often results in an application that is faster than the original Microsoft Access application.

The alternative to using the Microsoft Jet database engine is to use ODBCDirect. ODBCDirect uses a Connection object that represents a pure connection to the destination Oracle database. When using ODBCDirect, the Microsoft Jet database engine is not loaded. When using ODBCDirect, all SQL statements are sent unaltered to the Oracle server for manipulation. These SQL statements are evaluated and interpreted on the Oracle server, reducing network traffic significantly. Only the subset of record information queried is sent back over the network to the Microsoft Access application.

Modifying the VBA code to use the ODBCDirect Connection object is not a straightforward procedure. The property and method list of the Connection object is very different to those of the CurrentDB and Database objects. In addition, because the SQL statements are sent over the wire for manipulation by the Oracle server they must be in the correct Oracle syntax. Therefore, you should consider the following:

To replace the CurrentDB or Database object references with the Connection object references:

  1. Create a global ODBCDirect Connection object reference as follows:

    Global connOracle as Connection
    
  2. Create an ODBCDirect work space as follows:

    Dim DSN As String
    Dim UID As String
    Dim PWD AS String
    
    DSN = "ora817"
    UID = "system"
    PWD = "manager"
    
    wsODBC = DBEngine.CreateWorkspace(DSN, UID, PWD, dbUseODBC)
    
 
  1. Create an ODBCDirect Connection object by connecting to the Oracle database as follows:

    Dim ODBCconnectStr AS String
    
    ' build up the connect string
    ODBCconnectStr = "ODBC;DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD
    
    ' open a connection to the Oracle database
    Set connOracle = wsODBC.OpenConnection(DSN, dbDriverCompleteRequired, False, ODBCconnectStr)
    

  1. When manipulating data in the Oracle database, simply pass the SQL command directly to the Oracle server for processing as follows:

    Dim sql As String
    set sql = "select * from emp where empno > 10"
    connOracle.execute sql
    

5.5.1 Qualified and Restricted Queries

You can reduce network traffic by requesting only the columns you need from a table. You should also use the most restrictive qualifications possible to reduce the size of the query result set.

5.5.2 Snapshots Versus Dynasets

If possible, use Forward Only Snapshots to work with Oracle data, especially when the result set is small. For larger result sets and for queries that you must update, use a dynaset. Even if you are not going to update data, a dynaset is faster than a snapshot when the result set is large.

5.5.3 Comparing Use of Drop-Down Lists

Microsoft Access tries to minimize the amount of network traffic when it needs to populate a drop-down list option. When a snapshot is used to populate a drop-down list, Microsoft Access uses the same batch fetching of records that it uses to populate a grid or a form. Microsoft Access fetches an initial chunk of data (100 rows), then periodically retrieve sets of 100 rows from the server.

This process works smoothly unless you make an entry that does not match a row already fetched. In this case, Microsoft Access begins fetching records from the server until a match is found or until all records are retrieved. If the returned set is large, this step can be lengthy. It also may cause problems for you within the user interface.

Microsoft Access does not share queries for drop-down lists. A snapshot query is not reusable across multiple list boxes. Instead, Microsoft Access treats each activation of a query independently.

If a drop-down list is short, such as less than 100 records, it is probably sufficient to have Microsoft Access perform its normal operations. If the list is long, you may want to build a synchronized shadow table in Microsoft Access. Store the table information locally in Microsoft Access and periodically synchronize the local table with information from Oracle.