Sub TestByAdo()
Dim Select_String As String
Dim Sql_Driver As String
Sql_Driver = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
"DBQ=" & ThisWorkbook.FullName & ";READONLY=FALSE"
Select_String = _
" Update " & Get_Table([Tableau1_2]) & " Base , " & Get_Table([Tableau1]) & " Affectation " & _
" Set Base.Commentaire=affectation.Commenatire, " & _
" Base.Statut=affectation.Statut " & _
" Where Base.`code barre` = Affectation.`code barre`"
If Select_String <> "" Then
On Error Resume Next
Set Source_Folder = CreateObject("ADODB.Connection")
Source_Folder.Open Sql_Driver
Set Source_Filtre = CreateObject("ADODB.Recordset")
Source_Filtre.ActiveConnection = Source_Folder
Source_Filtre.Open Select_String
If Err <> 0 Then MsgBox "Erreur " & Err().Number & vbLf & Err().Description
Source_Filtre.Close
Set Source_Filtre = Nothing
Set Source_Folder = Nothing
End If
End Sub
Function Get_Table(Table As Object) As String
With Table
Get_Table = "[" & .Worksheet.Name & "$" & .ListObject.Range.Address(False, False) & "]"
End With
End Function