ACC2000: How to Programmatically Create,
Search, Replace, and Modify Code
|
The
information in this article applies to:
Advanced: Requires expert coding, interoperability, and
multiuser skills.
This article applies only to a Microsoft Access database (.mdb).
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.
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.
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 ensure that the Microsoft DAO 3.6 Object Library
check box is selected.
Microsoft
DAO 3.6 Object Library
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
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.
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:
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.
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
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.
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
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.
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