Using Find and Replace in an ADP to AccDb Migration

I’ve a long-term client I’m advising on their Microsoft Access development and sometimes develop some more complex, isolated functionality. Mainly I’m not involved in actual coding of the application, so I’m not familiar with its structure and most of the functionality.

This application was an Access Data Project (ADP). Recently they asked me to convert their application to the AccDb project type due to their planned migration to Access 2016.

I’ve quite a bit of experience of converting Access application from ADP to AccDB. However, usually these migration projects were applications I actively helped to develop and which I was very familiar with. I knew in advance what would work to convert most of the functionality and where problems were to be expected.

The actual details of the conversion process are beyond the scope of this text. So, let’s focus just on some basics here. The basic approach is to link all tables and views from SQL Server to the Access frontend via ODBC. Then modify the forms and reports to work with the linked tables instead of the SQL Server objects directly. Most of the VBA code can stay as it is, but any references to CurrentProject.Connection need to be replaced with a reference to a custom Property returning an ADODB.Connection to the SQL Server database.

So, depending on the application, quite a few properties and code lines need to be replaced. Some of them need to be completely rewritten. This kind of project is the perfect scenario to test our new Find and Replace add-in for Microsoft Access.

The usual suspects to address in such a conversion:

• SQL Server Stored Procedures and Functions in Recordsource– and Rowsource-Properties; particularly if these require Input Parameters.
• SELECT statements using the recommended Schemaname.Objectname reference to SQL Server tables and views.
• Any direct data access in VBA code via CurrentProject.Connection.
• Any quoted identifiers using the older quote syntax instead of the more modern square brackets.

As I know that the Recordsource-, Rowsource-, and potentially UniqueTable-Properties are going to be a main focus during the whole migration, I got to the extra length and select only the relevant controls and those properties and save them as a Preset for custom search settings in Find and Replace. Thus, I can easily reuse them without having to select the controls and properties each time I need them manually.

Note: Be very careful when modifying the Recordsource property of forms and reports in Access. When you change it, either manually or with a tool like Find and Replace, Access will automatically reset dependent properties, like OrderBy. In an ADP application there quite a few properties dependent on the Recordsource, like ServerFilter, InputParameters, and potentially RecordSourceQualifier.

It is a quick and easy final step to simply remove the term “dbo.”, the schema name prefix, from all the Recordsources, once things like ServerFilter, InputParameters have been addressed. – But replace “dbo” only in the form and report properties, but not in the code (we retain the ADO functionality there).

This is easy to achieve with Find and Replace by replacing the term “dbo.” with nothing. The detailed selection of “Controls / Sections“ in the forms in Find and Replace allow me to simply exclude just the code behind the forms and replace that term in seconds.

In the same manner I replace all calls to CurrentProject.Connection in VBA with calls to a custom ADODB.Connection property I wrote for that purpose.

Other operations, such as addressing the “dbo.” prefix in code cannot be completely automated with Find and Replace. We need to consider whether they are in code we want to keep untouched (e.g. ADO-Commands) or code we need to adapt for the AccDb migration (e.g. call to domain aggregate functions). Nevertheless, searching for the term brings up the matches in the Result List, which can be filtered further, and I can easily jump to each code locations and manually adjust the code using the “Goto Value” feature from the context menu. – Once again, a huge time saver.

During the implementation I also used Find and Replace for countless mundane tasks in navigating an unfamiliar project. Questions like …

• “in which locations is this table used?”
• “What is the parent report to this sub-report?”
• “Is this form referenced anywhere at all?”

can all be answered in seconds. These and similar operations using Find and Replace in this migration project saved me a huge amount of time and helped me significantly to deliver a (almost) error free converted application to the customer within time and budget.

Of course, there is more to an ADP to AccDb migration than just searching and replacing some terms and expressions. There are some real development tasks to complete as well:

• Tables and Views need to be automatically relinked after schema changes.
• Form and report data binding to SQL Server Functions and Stored Procedures needs to be changed and/or rewritten.
• Complex ad-hoc SQL queries should be replaced by views.
• Some tables need to be amended with timestamp columns to prevent write-conflicts.

To name the most common.

Tools like Find and Replace do not replace experience and knowledge of Access and SQL Server. They are extremely helpful and maximize the efficiency of experienced developers working on a project.