MS-Access Report and Form Record Source (and control Row Source) properties that use in-line SQL will not be converted to DB2 views or procedures 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 Forms box to create new QueryDef objects from Record Sources on Forms that are not updatable; that is 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; linked ODBC objects must be used in order to perform database maintenance.
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 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 Build List box to create the Where-Used table. This table is used to determine which Queries can be converted to DB2 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.