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
| Function MajLienODBC(PathBaseAModifier As String, TitreTableAModifier As String, Connect As String, TitreTableSource As String) As Boolean
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim temp As Boolean
Dim tables As DAO.TableDef
Dim relation As DAO.relation
Dim rel As DAO.relation
Dim chp As DAO.Field
Dim a As DAO.Property
Dim position As Long
On Error GoTo fin
MajLienODBC = False
temp = False
' Ouverture de la base de données
Set db = DBEngine.OpenDatabase(PathBaseAModifier)
For Each tables In db.TableDefs
If tables.Name = TitreTableAModifier Then
temp = True
Exit For
End If
Next
' Création d'un nouvel objet TableDef.
Set tbl = db.CreateTableDef(TitreTableAModifier & "_piou")
' Définition des propriétés pour créer le lien
tbl.Connect = Connect
tbl.SourceTableName = TitreTableSource
'Ajout de la nouvelle table
db.TableDefs.Append tbl
db.TableDefs.Refresh
If temp Then
'Création des relations identiques à l'ancienne table
For Each relation In db.Relations
If relation.table = TitreTableAModifier Then
Set rel = db.CreateRelation(tbl.Name & relation.ForeignTable, tbl.Name, relation.ForeignTable)
rel.Attributes = relation.Attributes
Set chp = rel.CreateField(relation.Fields(0).Name) ' Fournit le nom du champ clé externe.
chp.ForeignName = relation.Fields(0).ForeignName
rel.Fields.Append chp
db.Relations.Append rel
db.Relations.Refresh
End If
If relation.ForeignTable = TitreTableAModifier Then
Set rel = db.CreateRelation(relation.table & tbl.Name, relation.table, tbl.Name)
rel.Attributes = relation.Attributes
Set chp = rel.CreateField(relation.Fields(0).Name) ' Fournit le nom du champ clé externe.
chp.ForeignName = relation.Fields(0).ForeignName
rel.Fields.Append chp
Debug.Print rel.Name
db.Relations.Append rel
End If
Next
'Suppression des relations de l'ancienne table
For Each relation In db.Relations
If relation.table = TitreTableAModifier Or relation.ForeignTable = TitreTableAModifier Then
db.Relations.Delete relation.Name
End If
Next
'Suppression de l'ancienne table
db.TableDefs.Delete TitreTableAModifier
'Renommage de la nouvelle table
db.TableDefs(TitreTableAModifier & "_piou").Name = TitreTableAModifier
' Renommage des relations sans le _piou
For Each relation In db.Relations
If Replace(relation.Name, "_piou", "") <> relation.Name Then
Set rel = db.CreateRelation(Replace(relation.Name, "_piou", ""), Replace(relation.table, "_piou", ""), Replace(relation.ForeignTable, "_piou", ""))
rel.Attributes = relation.Attributes
Set chp = rel.CreateField(relation.Fields(0).Name) ' Fournit le nom du champ clé externe.
chp.ForeignName = relation.Fields(0).ForeignName
rel.Fields.Append chp
Debug.Print rel.Name
db.Relations.Delete relation.Name
db.Relations.Append rel
db.Relations.Refresh
End If
Next
MajLienODBC = True
End If
db.Close
Exit Function
fin:
End Function |
Partager