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
| Sub ChkLinkedTables()
Dim db As DAO.Database, tdef As DAO.TableDef
Dim ConnParams() As String, varParam As Variant, p As Long
Dim strLinkedDBFull As String, strLinkedDB As String, strPWD As String
Dim strNewLinkedDBFull As String, strConn As String
Dim colLinkedDBs As New Collection, strLinkedDBs As String
Dim strTitle As String
Set db = CurrentDb
For Each tdef In db.TableDefs
If (tdef.Attributes And dbAttachedTable) = dbAttachedTable Then
ConnParams() = Split(tdef.Connect, ";")
For Each varParam In ConnParams
' Récupérer chemin base
If varParam Like "DATABASE=*" Then
strLinkedDBFull = Mid(varParam, 10, Len(varParam) - 9)
' Si nouvelle base vérifie chemin
If InStr(1, strLinkedDBs, strLinkedDBFull) = 0 Then
If Dir(strLinkedDBFull) = "" Then
' Si base introuvable demande nouvel emplacement
p = InStrRev(strLinkedDBFull, "\")
If p > 1 Then
strLinkedDB = Mid(strLinkedDBFull, p + 1, Len(strLinkedDBFull) - p)
End If
strTitle = "Selectionner la base <" & strLinkedDB & "> depuis nouvel emplacement"
strNewLinkedDBFull = OuvrirFichier(strTitle, , "Access")
Else
' Sinon nouvel emplacement = emplacement actuel
strNewLinkedDBFull = strLinkedDBFull
End If
' ajoute base à la liste des bases vérifiées
strLinkedDBs = strLinkedDBs & strLinkedDBFull & ";"
colLinkedDBs.Add strNewLinkedDBFull, strLinkedDBFull
End If
End If
Next
End If
Next
For Each tdef In db.TableDefs
If (tdef.Attributes And dbAttachedTable) = dbAttachedTable Then
ConnParams() = Split(tdef.Connect, ";")
strPWD = "": strLinkedDBFull = ""
For Each varParam In ConnParams
' Récuperer mot de passe si existe
If varParam Like "PWD=*" Then
strPWD = varParam
End If
' Récupérer chemin base
If varParam Like "DATABASE=*" Then
strLinkedDBFull = Mid(varParam, 10, Len(varParam) - 9)
End If
Next
strNewLinkedDBFull = colLinkedDBs(strLinkedDBFull)
' Si nouvel emplacement <> emplacement actuel et nouvel
' emplacement <> "" alors refaire le lien
If strNewLinkedDBFull <> strLinkedDBFull And _
strNewLinkedDBFull <> "" Then
strConn = ";"
If strPWD <> "" Then strConn = strConn & strPWD & ";"
strConn = strConn & "DATABASE=" & colLinkedDBs(strLinkedDBFull)
' rafraichir lien
tdef.Connect = strConn
tdef.RefreshLink
End If
End If
Next
Set colLinkedDBs = Nothing
End Sub |
Partager