MS-Access Report and Form Record Source (and control Row Source) properties that use inline SQL will not be converted to Oracle views unless they are replaced by QueryDef objects. Scriptoria provides a utility that automates the creation (and substitution) of new QueryDef objects.
Choosing the Replace SQL with QueryDefs item from the Tools ! Migration Tools main menu or from the pop-up menu (right-click anywhere) displays the following form.
Check the Reports box to create new QueryDef objects from all Report Record Sources.
Check the Controls with SQL Row Sources box to create new QueryDef objects from all Report Controls that specify a Row Source value.
Check the Forms box to create new QueryDef objects from Record Sources.
Check the Controls with SQL Row Sources box to create new QueryDef objects from Form Controls that specify Row Source properties that are not updatable. This includes List Boxes and Combo Boxes that have the Limit To List property set to True and Graph objects.
Check the non-updatable box to only include Forms where the Allow Edits, Allow Deletions, and Allow Insertions properties are all set to False. Note that updatable Forms and Controls cannot be converted to views or procedures since linked ODBC objects must be used in order to perform database maintenance.
Check the Build List box to create the Where-Used table. This table is used to determine which Queries can be converted to Oracle without compromising any required update capabilities - Access linked objects must be used for ODBC updates.
Check the Search Modules box to find occurrences of QueryDef names in VBA code. Note that this process does not necessarily identify QueryDefs, but any string with the same name as a QueryDef. (see Where-Used List Builder.)
Press the OK button to initiate the QueryDef creation and substitution process, and optionally build the Where-Used list.
Press the Show Log button after the operation completes to display a report detailing the changes made to the Access database.
Remarks:
The name of the new QueryDef replaces the inline SQL. For Report and Form Row Sources, the QueryDef name defaults to the Report or From name, although name uniqueness is ensured. The QueryDef name for Controls combines the Report or Form name and Control name separated by the "@" character; ex. Products@CategoryList.
Record or Row Sources that duplicate the SQL of other Record or Row Sources are replaced by the name of the QueryDef that has already been created from the same inline SQL, thereby avoiding duplicate QueryDefs creation.
The QueryDef Description property shows the name of the Form, Report, and Control preceded by the string "Created from ".
The operation of the Access application does not change.