Retain MS-Access Front-End:
Check this box If you plan to retain the MS-Access front-end.
Use Module:
In order to replicate the behavior of Access parameter queries that operate with Oracle Views, Scriptoria needs to create VBA (Visual/Access Basic) functions. Scriptoria creates new Access QueryDefs that pass parameter values to the VBA functions.
An Access module must be specified to contain these functions. Although any module will work, creating a new module for the parameter query interface functions is recommended.
Views:
Oracle Views are automatically selected when the Retain MS-Access Front-End box is checked. The views retrieve parameter values passed by VBA functions using Oracle packages.
Procedures / Functions:
If you plan to replace the MS-Access front-end, you can choose to generate Oracle procedures/functions and packages from MS-Access queries that return results. The procedures or functions are implemented using Oracle Referenced Cursors.
Oracle procedures are generated for all MS-Access Action (Make Table, Update, Append, and Delete) and Variable Column Crosstab queries.
When Functions or Procedures are specified, you can choose to create an Oracle package to contain them.
No Queries:
When the No Queries option is selected, Scriptoria creates separate and Views, Functions or Procedures as specified in the Query Conversion Options section. A package is not created.
Parameter Queries Only:
Choose the Parameter Queries Only option when you wish to create a package that contains MS-Access queries that use parameters and return result sets.
All Queries:
When the All Queries option is selected, a package is created that contains Procedures and/or Functions for all converted MS-Access queries.
Package Name:
The Package Name defaults to the MS-Access database file name. You can change this value to any valid Oracle identifier.
The target tables of Access MakeTable queries can be created as permanent or global temporary Oracle tables. Global temporary tables enable concurrent usage without users interfering with each others work.
Access MakeTable and Append QueryDefs save result-sets to external MS-Access databases when the "Another Database" option is specified. Although this is an appropriate technique for Access development, it is sometimes a sign that the Access database should be re-engineered before converting to Oracle.
For example, an application that was originally designed for use by a single company may create new Access databases for each company that purchases the product. This application may also be used by some organizations to track multiple companies. Creating multiple copies of the same tables in different Access databases violates the first rule of relational database design which is to minimize data redundancy. A new design that consolidates all companies into a single database is better suited for conversion to Oracle.
In some cases, however, like when storing daily and historical data for instance, the original Access design may be appropriate for conversion by separating daily and historical data into different schemas. To accomplish this you must first convert the external target databases to Oracle specifying different Owner/Schema names for the tables in each external database. You may then open the database that contains the MakeTable and Append queries that create the external tables and use the Assign Owner/Schema Names to External Tables form to assign the Owner/Schema names previously specified for the corresponding external databases.
After assigning Owner/Schema names, the Oracle procedures generated by Scriptoria will specify for data to be selected from tables in the current schema for insertion into the tables residing in the schemas you assigned.
Some modifications to the applications that use VBA code to link to the external databases will also be required since ODBC linked objects cannot be refreshed in the same manner as linked Access tables. Some procedure changes may also be required when used in conjunction with Global Temporary tables.
You can choose to whether ORDER BY clauses are included in generated Oracle views or by the corresponding Access QueryDef that has been adapted to use the Oracle view when Scriptoria generates the new Access application. This option lets you decide whether to off-load the sorting operation to the workstation or to run it on the Oracle server. The options are Server or Client.
Scriptoria creates Oracle global temporary tables to implement variable column Crosstab queries using the maximum number of columns allowed by either Access or Oracle; 250 and 1000 respectively. The default is Access (250). If the Oracle maximum is chosen, the global temporary table cannot be viewed using the Scriptoria Object Viewer since it exceeds the maximum field limit. The pass-through query that implements the variable column Crosstab will operate as long as there are less than 250 fixed plus variable columns.
Note that the first row of the last (max) column contains the select statement provides the final summary required for implementation of the variable Crosstab query. When using Access as the front-end, Scriptoria automatically creates a pass-through query that fully implements the variable Crosstab query.