Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

You will find part 1 in Code and Code Windows

These examples are intended to make life easier, not for live databases. I have only tested very lightly indeed.

If you are lazy in a peculiar way, you can use VBE to add code. You will need some Reference functions for these examples, which can be found in Add, Remove, Check References. Note that this example also shows how to search through the code.

Example: Add some lines required for DAO

  Sub AddDAO(ProcName As String)
  Dim oVBE As Object
  Dim mdl As Object
  Dim blnFound As Boolean
  Dim lngLine As Long
  Dim strMdlName As String
  Dim strDAO As String
   
       'To make life easier and lines shorter.
      Set oVBE = VBE.ActiveVBProject.VBComponents
   
      'The code to be inserted.
      strDAO = "'Requires Microsoft DAO 3.x Object Library" & vbCrLf _
      & vbTab & "Dim db As DAO.Database" & vbCrLf _
      & vbTab & "Dim rs as DAO.Recordset" & vbCrLf & vbCrLf _
      & vbTab & "Set db = CurrentDB" & vbCrLf _
      & vbTab & "Set rs = db.Openrecordset("""")" & vbCrLf
   
      'Check each module ...
      For Each mdl In oVBE
          'for the required procedure ...
          lngLine = 1
          blnFound = oVBE(mdl.Name).CodeModule.Find("AddDAOToThisSub", lngLine, 1, 60, 1)
          'if it is found. ...
          If blnFound = True Then
              'make sure there is a Reference for DAO ...
              If RefExists("DAO") = False Then
                  ReferenceFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
              End If
              'the line number returned by find: lngLine
              'and insert the code.
              oVBE(mdl.Name).CodeModule.InsertLines (lngLine + 2), strDAO
          End If
      Next
  End Sub 

Databases can be added as References, and once this is done, you can access the code.

Example: Check Code in Another Database See also : http://wiki.lessthandot.com/index.php/Search_All_Databases_in_Folder_for_Words_in_Code_Modules

  Sub CheckCodeInAnExternalModule()
  Dim oVBE As Object
  Dim mdl As Object
  Dim blnFound As Boolean
  Dim StartLine As Long
  Dim StartColumn As Long
  Dim EndLine As Long
  Dim EndColumn As Long
  Dim ref As Reference
  Dim strDBFullPath
  Dim strStringToFind
   
      'To make life easier and lines shorter.
      'Set oVBE = VBE.ActiveVBProject.VBComponents
   
      'The DB to reference
      strDBFullPath="C:\Docs\LTD.mdb"
   
      'Adding the reference will cause an error.
      On Error Resume Next
      
      Set ref = References.AddFromFile(strDBFullPath)
      
      'You can put the error handler back here.    
   
      Set oVBE = VBE.VBProjects(ref.Name).VBComponents
      
      strStringToFind = "Connect"
   
      'Check each module ...
      For Each mdl In oVBE
          EndLine = oVBE(mdl.Name).CodeModule.CountOfLines
          'check each line ...
          For i = 1 To EndLine
          Ln = oVBE(mdl.Name).CodeModule.Lines(i, 1)
          If InStr(1, Ln, strStringToFind) > 0 Then
              'if the line contains the string to find, Debug.Print.
              Debug.Print mdl.Name
              Debug.Print Ln & "(" & i & ")"
          End If
          Next
       Next
      
      'Remove the reference to the database.
      References.Remove ref
  End Sub

There is a collection.

Snippet: VB Projects Collection

  For Each proj In VBE.VBProjects
      If proj.Name <> CurrentProject.Name Then
          Debug.Print proj.Name
      End If
  Next
   
  

Further Information

VBE Property: http://msdn2.microsoft.com/en-us/library/aa220212(office.10).aspx