Upgrading MDBs to ADPs, search and replace in forms/controls

[ESquared]

I had to convert an MDB to an ADP once, and wrote some tools to help me with it. I'll share my experience with you in case it is useful.

Here are some general outlines of a semi-automated process you can write to help you with converting an MDB to ADP.

Write a procedure to:

• Open every form in design view
• Step through every control
• Document all control types found in the project for personal information, such as in case any activeX controls are hidden somewhere. Perhaps explore naming conventions of controls.
• Save all RowSource and RecordSource properties of appropriate type controls to a single text file for review.
• Load the text file in Query Analyzer and parse it, find errors en masse, see common problems.
• Devise plan for replacing common changes such as " to ' and & to +.
• Go back to control-looping code and add code to stop when one of the easily fixable conditions is found and print it to the debug window. If fixing automatically is okay, hit F5 to replace it, otherwise Ctrl-F9 to appropriate place to skip to next control/form.

I not only fixed SQL Server-specific syntax errors semi-automatically, but took the opportunity to change the names of some objects.

I later took that same code I'd written and made a generic search-and-replace function that works across my entire project, all forms AND reports, too. It's even aware of all 7 or 8 section types that can appear on these things.

One problem I haven't dealt with is truly supporting the "onlyopenforms" option, since before I wasn't stepping into subforms since they'd be caught by themselves. But I'll eventually fix it so the list of forms to work with is auto-discovered and then checked off during traversal, and subforms are stepped into only if they haven't been traversed before.

Here's a teaser from my searchandreplace procedure, to spur ideas.

Private Sub SearchAndReplace()
   Dim aoF As AccessObject
   Dim frmF As Object
   
   Dim sFindString As String
   Dim sReplaceWhat As String
   Dim sReplaceWith As String
   Dim fDoReplace As Boolean
   Dim fShowAllNames As Boolean
   Dim fOnlyOpenForms As Boolean
   Dim fLeaveChangedFormsOpen As Boolean
   Dim fCheckForEXEC As Boolean
   Dim fAddEXECComments As Boolean
   Dim fShowNonCanonicalNames As Boolean
   Dim iNumberFound As Long
   Dim iNumberFoundBefore As Long
   
   'sFindString = "*WhatToSearchFor*" 'use when needing to not match with wildcards on both ends
   sReplaceWhat = "ReplaceWhat"
   sReplaceWith = "ReplaceWith"
   fDoReplace = False
   fShowAllNames = False
   fOnlyOpenForms = False
   fLeaveChangedFormsOpen = False
   fCheckForEXEC = False
   fAddEXECComments = False
   fShowNonCanonicalNames = False
   
   If sFindString = "" Then sFindString = "*" & sReplaceWhat & "*"

It writes to the debug window a list of all objects found, what was replaced (if anything, according to the options) and summarizes how many occurrences at the end. It shows form/report, control, controltype, property name, property value, replaced value. It knows what properties it can examine or set at design time and which it can't, skipping over those.

EXEC Comments: for each RowSource or RecordSource it adds a single line comment stating the form and control. Makes analysis in Query Profiler of the source of queries a snap. It can remove or update these EXEC comments, too, for controls that get moved/changed/copied.

You can guess at what the other options do.

This code has allowed me to actually and easily change object names in the middle of a project (based on new understanding of a business entity or a change in the design imposed by the client). Since I think naming is so important, it was really wonderful to have the luxury of doing this. Just be careful to not collide with user-visible names, although care taken in naming can help avoid that. Of course, the option to do a mock search and replace, showing what *would* be replaced is wonderful. Run it once, validate everything is okay, then run it again for real.

Feel like changing your fonts in the entire project? Can do.

Need to know where something is referenced? Find it.

Want to change all of one color to another? Do it in minutes.

I have used the search and replace procedure over and over again!

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]