[ACCESS] ATTACHER / DÉTACHER DES TABLES LIÉES

Public Function VerifAttach() As Boolean
Dim tdf As DAO.TableDef, strTemp As Variant, strPath As String, i As Long

For Each tdf In CurrentDb.TableDefs
    ' recherche d'une table liée 
    If tdf.Connect <> "" Then
        strTemp = Split(tdf.Connect, ";")
        For i = LBound(strTemp) To UBound(strTemp)
            ' recherche du paramètre de connection
            If strTemp(i) Like "DATABASE=*" Then
                strPath = Split(strTemp(i), "=")(1)
                ' vérification de l'existence de la bdd Coclico
                If Dir(strPath) <> "" Then
                    Coclico_VerifAttach = True
                    Exit Function
                End If
            End If
        Next i
    End If
Next

End Function




Public Sub DeleteTables()
' supprimer toutes les tables attachées
On Error Resume Next

Dim db As DAO.Database  'Database to import
Dim tdf As DAO.TableDef
Dim arrTablename() As String, i As Long

ReDim arrTablename(0)
Set db = CurrentDb
' répertorier les tables à supprimer
For Each tdf In db.TableDefs
    If tdf.Connect <> "" Then
        ReDim Preserve arrTablename(UBound(arrTablename) + 1)
        arrTablename(UBound(arrTablename)) = tdf.Name
    End If
Next
' suppression
For i = LBound(arrTablename) To UBound(arrTablename)
    db.TableDefs.Delete arrTablename(i)
Next i
Set db = Nothing

End Sub



Public Function ActualiserAttaches(ByVal strCheminBd As String, Optional ByVal strMotPasse As String = "") As Boolean

On Error GoTo ActualiserAttaches_Err
Dim tdf As DAO.TableDef

Dim arrTablename As Variant, arrSourceName As Variant, strSourceConnect As String, i As Long
arrTablename = Array("Activite", "RaisonSociale", "Region", "Site")
arrSourceName = Array("Activités", "Raison_sociale", "Region", "Site")
strSourceConnect = "MS Access;PWD=" & strMotPasse & ";DATABASE=" & strCheminBd

' supprimer les tables avant tout
DeleteTables

' créer les tables
For i = LBound(arrTablename) To UBound(arrTablename)
    Set tdf = New TableDef
    tdf.Name = arrTablename(i)
    tdf.SourceTableName = arrSourceName(i)
    tdf.Connect = strSourceConnect
    CurrentDb.TableDefs.Append tdf
Next i
ActualiserAttaches = True
Exit Function

ActualiserAttaches_Err:
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in Function ActualiserAttaches of Module mdFonctions", vbCritical

End Function