1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
| Option Compare Database
Option Explicit
Private Sub UtilisationsChamps()
Dim tdf As Object, sNomChamp As String, k As Integer
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then ' Don't enumerate the system tables
For k = 0 To tdf.Fields.Count - 1
sNomChamp = tdf.Fields(k).Name
Debug.Print "=== Table: " & tdf.Name & " === Champ: " & sNomChamp
FindWordInModules sNomChamp
FindIntoQueries sNomChamp
FindIntoForm sNomChamp
Debug.Print
Next k
End If
Next tdf
End Sub
Public Sub FindWordInModules(ByVal sTxt As String)
'Dim objComponent As VBComponent
' VBComponent requires reference to Microsoft Visual Basic
' for Applications Extensibility; use late binding instead:
'--- http://www.cpearson.com/excel/vbe.aspx
Dim objComponent As Object
Dim CodeMod As Object
Dim SL As Long ' start line
Dim EL As Long ' end line
Dim SC As Long ' start column
Dim EC As Long ' end column
Dim Found As Boolean
Debug.Print "--- MODULES ---"
For Each objComponent In Application.VBE.ActiveVBProject.VBComponents
Set CodeMod = objComponent.CodeModule
With CodeMod
SL = 1
EL = .CountOfLines
SC = 1
EC = 255
Found = .Find(Target:=sTxt, StartLine:=SL, StartColumn:=SC, _
EndLine:=EL, EndColumn:=EC, _
wholeword:=False, MatchCase:=False, patternsearch:=False)
Do Until Found = False
Debug.Print objComponent.Name & ", ligne " & CStr(SL) & ", colonne " & CStr(SC)
EL = .CountOfLines
SC = EC + 1
EC = 255
Found = .Find(Target:=sTxt, StartLine:=SL, StartColumn:=SC, _
EndLine:=EL, EndColumn:=EC, _
wholeword:=False, MatchCase:=False, patternsearch:=False)
Loop
End With
Next objComponent
End Sub
Public Sub FindIntoQueries(ByVal sTxt As String)
Dim Qry As Object
Debug.Print "--- QUERIES ---"
For Each Qry In CurrentDb.QueryDefs
If InStr(Qry.sql, sTxt) > 0 Then
Debug.Print "--- " & Qry.Name
Debug.Print Qry.sql
End If
Next Qry
End Sub
Public Sub FindIntoForm(ByVal sTxt As String)
Dim Frm As Object, sNom As String, sSource As String
Debug.Print "--- FORMS ---"
For Each Frm In CurrentProject.AllForms
sNom = Frm.Name
DoCmd.OpenForm sNom, acDesign
sSource = Forms(sNom).RecordSource
If InStr(sSource, sTxt) > 0 Then
Debug.Print "--- " & Frm.Name,
Debug.Print sSource
End If
FindIntoControl Forms(sNom), sTxt
DoCmd.Close acForm, sNom
Next Frm
End Sub
Public Sub FindIntoControl(Frm As Form, ByVal sTxt As String)
Dim Ctl As Control
On Error Resume Next
For Each Ctl In Frm.Controls
If Ctl.ControlType = acListBox Or Ctl.ControlType = acComboBox Then
If InStr(Ctl.RowSource, sTxt) > 0 Then
Debug.Print "--- " & Frm.Name & "." & Ctl.Name
Debug.Print Ctl.RowSource
End If
End If
Next Ctl
End Sub |
Partager