ACC2000: How to Programmatically Create, Search, Replace, and Modify Code

View products that this article applies to.

This article was previously published under Q208793

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

Summary

This article gives you examples of how to programmatically create, search, replace, and modify Visual Basic for Applications code within Access.

One of the most common uses for code that itself writes code is a wizard. Wizards can be used to offer a variety of solutions that do not require the user to understand, or to have access to, product-development features. For example, a wizard can be used to create custom toolbars and menu bars that meet specific user requirements.

↑ Back to the top

More information

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Determining what code to write based on your users' requirements will be left up to you, the application designer. This article concentrates on how to create a new subroutine in a new module. It will then demonstrate how to manipulate the code that has just been created.

How to Create a Subroutine in Code

This example creates a new module, inserts the code for a working subroutine, and then saves, closes, and renames the module.

The code in this example inserts the following subroutine in a module called Create Code:

Sub TestOpenDatabase()
    Dim DB As DAO.Database
    Set DB = CurrentDb
    MsgBox "The Database " & DB.Name & " opened successfully!"
    DB.Close
End Sub

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

In the Database window, click Modules, and then click New.
On the Tools menu, click References. Select the following reference:
Microsoft DAO 3.6 Object Library

In the new module, type the following:

Option Explicit
Dim MyModule As Module

Sub MakeCode()

   Dim strIndent As String, strText As String

   ' Create 4 spaces for code indent.
   strIndent = "    "

   ' Build a string variable with the code to be written
   ' to the new module.
   strText = "Sub TestOpenDatabase()" & vbCrLf
   strText = strText & strIndent & "Dim DB As DAO.Database" & vbCrLf
   strText = strText & strIndent & "Set DB = CurrentDB" & vbCrLf
   strText = strText & strIndent & "MsgBox ""The Database "" & " & _
      "DB.Name & "
   strText = strText & strIndent & strIndent & """ opened " & _
      "successfully!""" & vbCrLf
   strText = strText & strIndent & "DB.Close" & vbCrLf
   strText = strText & "End Sub"

   ' Create a new Module.
   Application.RunCommand acCmdNewObjectModule

   ' Set MyModule to be the new Module Object.
    Set MyModule = Application.Modules.Item(Application.Modules.Count - 1)
   ' Insert the code string into the new module.
   MyModule.InsertText strText

   ' Save, close, and rename the new Module as "Created Code."
   
   DoCmd.Save acModule, MyModule

   DoCmd.Close acModule, MyModule, acSaveYes

   DoCmd.Rename "Created Code", acModule, MyModule

End Sub

Save the module as CodeMaker.
With the mouse pointer anywhere within the MakeCode subroutine, press F5 to run the code.
To see the TestOpenDatabase subroutine just created, open the Created Code module.
Run the TestOpenDatabase subroutine.

NOTE: You cannot have multiple modules or subroutines with the same name. Before you run the MakeCode procedure in this first example a second time, delete the Created Code module.

How to Search for a Code String in a Module

This example uses the Find method to locate a string in a module.

Because the Find method is not aware of where any subroutine starts or ends, and because there may be multiple copies of the string you are searching for in the module, you must take care in selecting the correct string. Once the Find method locates the string, it returns the string's line number within the module.

To search for a string within a specific subroutine within a module, follow these steps:

Use the Find method to locate and save the line number for the first instance of the name of a specific subroutine.
Use the Find method to locate and save the line number of the first instance of the string "End Sub" starting at the line number captured in step a by using the Find method's startline argument.
Use the Find method to locate and save the line number of the string you are searching for within the desired subroutine by specifying the startline from step a and the endline from step b by using the Find method's startline and endline arguments.

In the Find method, the target, startline, startcolumn, endline, and endcolumn arguments are not optional and are updated to the matching string values when Find locates the string.

For more information about the Find Method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type Find Method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

In this example, the code searches the module created in the previous example for the string "DB.Close" and inserts the line of code, "Set DB = Nothing" on the following line. It will then save and close the Created Code module.

Open the CodeMaker module and type the following lines after the code that you inserted in the previous example:


Sub SearchCode()
  Dim StartLine As Long, StartColumn As Long
  Dim EndLine As Long, EndColumn As Long
  
  
  ' Open the Module you want to modify.
  DoCmd.OpenModule "Created Code"

  ' Set the Created Code Modules as the Object.
  Set MyModule = Application.Modules("Created Code")
 
  ' Search for string "DB.Close".
  If MyModule.Find("DB.Close", StartLine, StartColumn, _
     EndLine, EndColumn) Then

     ' If string is found, insert new line of code using the same
     ' column indent.
     MyModule.InsertLines StartLine + 1, _
        String(StartColumn - 1, " ") & "Set DB = Nothing"
  Else
     MsgBox "Text not found."
  End If

  ' Save and close the module.
  DoCmd.Save acModule, MyModule
  DoCmd.Close acModule, MyModule
  
End Sub

Save the CodeMaker module.
With the mouse pointer anywhere within the SearchCode subroutine, press F5 to run the code.
To see the TestOpenDatabase subroutine that was just modified, open the Created Code module.
Run the subroutine TestOpenDatabase.

How to Replace a Line of Code in a Module

This example uses the Find method to locate the string " Set DB = "" " and replaces the entire line of code with the following line:

Set DB = DBEngine.OpenDatabase("C:\Program Files\Microsoft Office\" & _
"Office\Samples\Inventry.mdb")

This example requires that the Inventry.mdb database is in the location specified in the preceding line of code. If this is not the case, modify the example below to point to a valid database path and file.

Open the CodeMaker module and type the following lines after the code that you inserted in the previous example:

Sub ReplaceCode()
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long

' Open the Module you want to modify.
DoCmd.OpenModule "Created Code"

' Set the Created Code Modules as the Object.
Set MyModule = Application.Modules("Created Code")

' Search for string "Set DB =".
If MyModule.Find("Set DB =", StartLine, StartColumn, EndLine, _
   EndColumn) Then

  ' If string is found, insert new line of code using the same
  ' column indent.
  MyModule.ReplaceLine StartLine, String(StartColumn - 1, " ") & _
     "Set DB = DBEngine.OpenDatabase(""C:\Program Files\" & _
     "Microsoft Office\"" & _" _
     & vbCrLf & "        ""Office\Samples\Inventry.mdb"")"

Else
  MsgBox "Text not found."
End If

' Save and close the module.
DoCmd.Save acModule, MyModule
DoCmd.Close acModule, MyModule, acSaveYes

End Sub

Save the CodeMaker module.
With the mouse pointer anywhere within the ReplaceCode subroutine, press F5 to run the code.
To see the TestOpenDatabase subroutine that was just modified, open the Created Code module.
Run the TestOpenDatabase subroutine.

How to Modify a Line of Code in a Module

This example uses the Find method to locate the string "Inventry.mdb" and to replace it with "Northwind.mdb".

To accomplish this, the code has to disassemble the located line of text, remove the old string, and reconstruct the line with the new string. As before, it will save and close the module after making the changes.

This example requires that the Inventry.mdb and Northwind.mdb databases are in the location specified in the preceding example. If this is not the case, modify the example below to point to valid database paths and files.

Open the CodeMaker module and type the following lines after the code that you inserted in the previous example:

Sub ModifyCode()
   Dim StartLine As Long, StartColumn As Long
   Dim EndLine As Long, EndColumn As Long
   Dim strLine As String, strNewLine As String
   Dim intChr As Integer, intBefore As Integer, intAfter As Integer
   Dim strLeft As String, strRight As String
   Dim strSearchText As String, strNewText

   ' The string you are searching for is:
   strSearchText = "Inventry.mdb"

   ' The replacement string is:
   strNewText = "Northwind.mdb"

   ' Open the Module you want to modify.
   DoCmd.OpenModule "Created Code"

   ' Set the Created Code Modules as the Object.
   Set MyModule = Application.Modules("Created Code")

   ' Search for string.
   If MyModule.Find(strSearchText, StartLine, StartColumn, EndLine, _
      EndColumn) Then

    ' Store text of line containing string.
    strLine = MyModule.Lines(StartLine, Abs(EndLine - StartLine) + 1)

    ' Determine length of line.
    intChr = Len(strLine)

    ' Determine number of characters preceding search text.
    intBefore = StartColumn - 1

    ' Determine number of characters following search text.
    intAfter = intChr - CInt(EndColumn - 1)

    ' Store characters to left of search text.
    strLeft = Left$(strLine, intBefore)

    ' Store characters to right of search text.
    strRight = Right$(strLine, intAfter)

    ' Construct string with replacement text.
    strNewLine = strLeft & strNewText & strRight

    ' Replace the original line.
    MyModule.ReplaceLine StartLine, strNewLine

   Else
      MsgBox "Text not found."
   End If

   ' Save and close the module.
   DoCmd.Save acModule, MyModule
   DoCmd.Close acModule, MyModule, acSaveYes

End Sub

Save the CodeMaker module.
With the mouse pointer anywhere within the ModifyCode subroutine, press F5 to run the code.
To see the TestOpenDatabase subroutine that was just modified, open the Created Code module.
Run the subroutine TestOpenDatabase.

↑ Back to the top

References

For more information about the searching for, inserting, or replacing of lines of code, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Find Method, InsertText Method, ReplaceLine Method, or Lines Property" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top