Sub ImportTableAccess_V03()
Dim Conn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim Fichier As String, rSQL As String
Dim numDevis As Long
Range('A5:F14').ClearContents
If Not IsNumeric(Range('B2')) Then Exit Sub
Fichier = ThisWorkbook.Path & '\\bd.mdb'
numDevis = Range('B2')
Set Conn = New ADODB.Connection
With Conn
.Provider = 'Microsoft.Jet.OLEDB.4.0'
.Mode = adModeRead
.Properties('Jet OLEDB:Database Password') = 'TOTO'
.Open Fichier
End With
rSQL = 'SELECT Table2.[N° FACTURE],Table2.[DATELIVRAISON],Table2.[DATEFACTURE],' & _
'Table2.[MODE PAIEMENT (1=CHQ, 2=CB)]' & _
' FROM Table1 , Table2' & _
' WHERE Table1.ID=Table2.IDREGISTRE AND Table1.[N°DEVIS]='
Set rsT = New ADODB.Recordset
With rsT
.ActiveConnection = Conn
.Open rSQL & numDevis, , adOpenStatic, adLockOptimistic, adCmdText
End With
If rsT.EOF Then
MsgBox 'Le numero de facture ' & numDevis & ' n'a pas été trouvée'
rsT.Close
Conn.Close
Exit Sub
End If
Range('A5') = numDevis
Cells(5, 2) = rsT.Fields(0).Value
Cells(5, 3) = rsT.Fields(1).Value
Cells(5, 4) = rsT.Fields(2).Value
Cells(5, 6) = rsT.Fields(3).Value
rsT.Close
Conn.Close
End Sub