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 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
| Private Sub Command1_Click()
Dim MaConn As ADODB.Connection, rstTable As ADODB.Recordset
Dim rstEnfant As ADODB.Recordset, rstCompl As ADODB.Recordset
Dim cmpt1 As Long, cmpt2 As Long, NomTable As String, Indexed As Boolean
Dim cleNoeudTable As String, cleNoeudChamps As String, cleNoeudCles As String
Dim cleNoeudChampCourant As String, cleNoeudCleCourante As String
'création de la connexion
Set MaConn = New ADODB.Connection
MaConn.Provider = "Microsoft.Jet.OLEDB.4.0;"
MaConn.Open Text1.text
'création du recordset contenant la structure des tables
Set rstTable = New ADODB.Recordset
Set rstTable = MaConn.OpenSchema(adSchemaTables)
'Ajout de la racine du treeview
TreeView1.Nodes.Add , , "r", "Structure"
TreeView1.Nodes(1).Expanded = True
'Parcours de la collection des tables
cmpt1 = 1
Do While Not rstTable.EOF
NomTable = rstTable!Table_Name
'élimine les tables systèmes et les Vues
If InStr(1, NomTable, "MSYS", vbTextCompare) <> 1 And rstTable!Table_Type <> "VIEW" Then
'Ajout de la table au treeview
cleNoeudTable = "t" & cmpt1
TreeView1.Nodes.Add "r", tvwChild, cleNoeudTable, NomTable
'création du recordset des champs de la table
Set rstEnfant = New ADODB.Recordset
Set rstEnfant = MaConn.OpenSchema(adSchemaColumns, Array(Empty, Empty, NomTable, Empty))
cmpt2 = 1
cleNoeudChamps = cleNoeudTable & "ch"
cleNoeudCles = cleNoeudTable & "cl"
TreeView1.Nodes.Add cleNoeudTable, tvwChild, cleNoeudChamps, "champs"
TreeView1.Nodes.Add cleNoeudTable, tvwChild, cleNoeudCles, "clés"
Do While Not rstEnfant.EOF
'ajoute au treeview le champ et ses caractéristiques
cleNoeudChampCourant = cleNoeudChamps & cmpt2
TreeView1.Nodes.Add cleNoeudChamps, tvwChild, cleNoeudChampCourant, rstEnfant!COLUMN_NAME
TreeView1.Nodes.Add cleNoeudChampCourant, tvwChild, cleNoeudChampCourant & "n", _
"peut être NULL -> " & rstEnfant!IS_NULLABLE
TreeView1.Nodes.Add cleNoeudChampCourant, tvwChild, cleNoeudChampCourant & "t", _
"Type -> " & Switch(rstEnfant!DATA_TYPE = adInteger, "Long", _
rstEnfant!DATA_TYPE = adSmallInt, "Entier", _
rstEnfant!DATA_TYPE = adWChar, "String")
If Not IsNull(rstEnfant!CHARACTER_MAXIMUM_LENGTH) Then _
TreeView1.Nodes.Add cleNoeudChampCourant, tvwChild, cleNoeudChampCourant & "l", _
"Max Caractères -> " & rstEnfant!CHARACTER_MAXIMUM_LENGTH
If Not IsNull(rstEnfant!NUMERIC_PRECISION) Then
TreeView1.Nodes.Add cleNoeudChampCourant, tvwChild, cleNoeudChampCourant & "p", _
"Précision -> " & rstEnfant!NUMERIC_PRECISION
TreeView1.Nodes.Add cleNoeudChampCourant, tvwChild, cleNoeudChampCourant & "e", _
"Echelle -> " & rstEnfant!NUMERIC_SCALE
End If
'vérifie si le champ est indexé
Set rstCompl = New ADODB.Recordset
Set rstCompl = MaConn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, NomTable))
Indexed = False
Do While Not rstCompl.EOF
If rstCompl!COLUMN_NAME = rstEnfant!COLUMN_NAME Then
Indexed = True
TreeView1.Nodes.Add cleNoeudChampCourant, tvwChild, cleNoeudChampCourant & "i", _
"Indexé -> Oui" & IIf(rstCompl!Unique, " sans ", " avec ") & "doublons"
Exit Do
End If
rstCompl.MoveNext
Loop
If Not Indexed Then TreeView1.Nodes.Add cleNoeudChampCourant, tvwChild, _
cleNoeudChampCourant & "i", "Indexé -> Non"
rstCompl.Close
Set rstCompl = Nothing
cmpt2 = cmpt2 + 1
rstEnfant.MoveNext
Loop
rstEnfant.Close
cmpt2 = 2
'recherche les clés de la tables
Set rstEnfant = MaConn.OpenSchema(adSchemaPrimaryKeys, Array(Empty, Empty, NomTable))
If Not rstEnfant.BOF Then
'ajout de la clé primaire si elle existe
TreeView1.Nodes.Add cleNoeudCles, tvwChild, cleNoeudTable & "cp", _
"Clé primaire -> " & rstEnfant!COLUMN_NAME
End If
rstEnfant.Close
'Ajout des clés étrangères et de leurs caractéristiques
Set rstEnfant = MaConn.OpenSchema(adSchemaForeignKeys, _
Array(Empty, Empty, Empty, Empty, Empty, NomTable))
Do While Not rstEnfant.EOF
cleNoeudCleCourante = cleNoeudCles & cmpt2
TreeView1.Nodes.Add cleNoeudCles, tvwChild, cleNoeudCleCourante, _
"Clé Etrangère -> " & rstEnfant!FK_COLUMN_NAME
TreeView1.Nodes.Add cleNoeudCleCourante, tvwChild, cleNoeudCleCourante & "cl", _
"Clé de la table -> " & rstEnfant!PK_TABLE_NAME
TreeView1.Nodes.Add cleNoeudCleCourante, tvwChild, cleNoeudCleCourante & "ur", _
"Update Rules -> " & rstEnfant!UPDATE_RULE
TreeView1.Nodes.Add cleNoeudCleCourante, tvwChild, cleNoeudCleCourante & "dr", _
"Delete Rules -> " & rstEnfant!DELETE_RULE
cmpt2 = cmpt2 + 1
rstEnfant.MoveNext
Loop
cmpt1 = cmpt1 + 1
rstEnfant.Close
Set rstEnfant = Nothing
End If
rstTable.MoveNext
Loop
TreeView1.Style = tvwTreelinesPlusMinusText
End Sub |
Partager