|Oracle® Database SQL Developer Supplementary Information for Microsoft Access Migrations
Part Number E12154-01
This chapter includes the following sections:
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.
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:
|101||0||Do not allow the user to store the
|101||1||Allow the user to store the
|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.
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.
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.
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:
Create a global variable of type
Database. For example:
Global dbOracle As Database
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)
Replace all references to
CurrentDB with a reference to
Databaseobject 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.
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
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:
Modify all SQL statements within the VBA code to comply with Oracle SQL syntax.
Change SQL statements containing Microsoft Access specific expressions to Oracle syntax.
Replace built-in Microsoft Access functions used within a SQL statement with the equivalent Oracle functions.
To replace the
CurrentDB or Database object references with the
Connection object references:
Create a global
Connection object reference as follows:
Global connOracle as Connection
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)
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)
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
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.
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.