Private Sub CommandButton1_Click()
Dim NumCde As String
Dim Requete As String
NumCde = TextBox1.Value
Requete = "select ((T2.'GEST' || TO_CHAR(TRUNC(T2.'NU_CDE' ) )) || TO_CHAR(TRUNC(T2.'LIG_CDE' ) )) , T2.'MT_ENGAGE' - T3.'MT_LIQ' , T3.'MT_LIQ' , T2.'MT_ENGAGE' , T1.'NU_LIQ' , T2.'QTE_CDEE' , T2.'LIBELLE_LIGNE_CDE' , T1.'LIG_CDE' , T1.'NU_CDE' , T1.'GEST' , T2.'QTE_RECUE' from ('LIG_COMMANDE' T2 LEFT OUTER JOIN ('RECEP_CDE' T3 LEFT OUTER JOIN 'MANDATS_DE_COMMANDES' T1 on T3.'EH'=T1.'EH' and T3.'GEST'=T1.'GEST' and T3.'NU_CDE'=T1.'NU_CDE' and T3.'LIG_CDE'=T1.'LIG_CDE' and T3.'NU_RECEP'=T1.'NU_RECEP' and T3.'NUM_LIQ'=T1.'NU_LIQ') on T2.'EH'=T3.'EH' and T2.'GEST'=T3.'GEST' and T2.'NU_CDE'=T3.'NU_CDE' and T2.'LIG_CDE'=T3.'LIG_CDE') where T1.'NU_CDE'=" & NumCde _
& " order by 'LIG_CDE' asc"
'MsgBox (Requete)
End Sub
Private Sub CommandButton1_Click()
Dim NumCde As String
Dim Requete As String
NumCde = TextBox1.Value
Requete = "select ((T2.'GEST' || TO_CHAR(TRUNC(T2.'NU_CDE' ) )) || TO_CHAR(TRUNC(T2.'LIG_CDE' ) )) , T2.'MT_ENGAGE' - T3.'MT_LIQ' , T3.'MT_LIQ' , T2.'MT_ENGAGE' , T1.'NU_LIQ' , T2.'QTE_CDEE' , T2.'LIBELLE_LIGNE_CDE' , T1.'LIG_CDE' , T1.'NU_CDE' , T1.'GEST' , T2.'QTE_RECUE' from ('LIG_COMMANDE' T2 LEFT OUTER JOIN ('RECEP_CDE' T3 LEFT OUTER JOIN 'MANDATS_DE_COMMANDES' T1 on T3.'EH'=T1.'EH' and T3.'GEST'=T1.'GEST' and T3.'NU_CDE'=T1.'NU_CDE' and T3.'LIG_CDE'=T1.'LIG_CDE' and T3.'NU_RECEP'=T1.'NU_RECEP' and T3.'NUM_LIQ'=T1.'NU_LIQ') on T2.'EH'=T3.'EH' and T2.'GEST'=T3.'GEST' and T2.'NU_CDE'=T3.'NU_CDE' and T2.'LIG_CDE'=T3.'LIG_CDE') where T1.'NU_CDE'=" & NumCde _
& " order by 'LIG_CDE' asc"
With ThisWorkbook.Sheets(1)
.Cells.ClearContents
End With
With ActiveWorkbook.Connections("IN01 (Par défaut) COMMANDE").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array(Requete)
.CommandType = xlCmdSql
.Connection = "OLEDB;Provider=MSDAORA.1;User ID=MAG2;Data Source=IN01"
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
.ServerFillColor = False
.ServerFontStyle = False
.ServerNumberFormat = False
.ServerTextColor = False
End With
ActiveWorkbook.Connections("IN01 (Par défaut) COMMANDE").Refresh
End Sub
Private Sub ConnecterBase(ConnectBD As ADODB.Connection, _
CheminBD As String, _
Optional Rs)
Set ConnectBD = New ADODB.Connection
If Not IsMissing(Rs) Then
Set Rs = New ADODB.Recordset
End If
With ConnectBD
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = CheminBD
.Open
End With
End Sub
Private Sub Requete()
Dim ConnectBD As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim ChaineSQL As String
ChaineSQL = "SELECT Champ1, Champ2, Champ3 "
ChaineSQL = ChaineSQL & "FROM Table_Visee "
ChaineSQL = ChaineSQL & "WHERE Champ2 > 3500 "
ChaineSQL = ChaineSQL & "OR Champ3 < 60 "
ChaineSQL = ChaineSQL & "ORDER BY Champ1"
ConnecterBase ConnectBD, "C:\Dossier\SousDossier\MaBase.mdb", Rs
With Rs
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open ChaineSQL, ConnectBD
Do Until .EOF
Debug.Print .Fields("Champ1") _
& ", " & .Fields("Champ2") _
& ", " & .Fields("Champ3")
.MoveNext
Loop
End With
ConnectBD.Close
Set ConnectBD = Nothing
Set Rs = Nothing
End Sub
Private Sub CommandButton1_Click()
Dim NumCde As String
Dim Requete As String
NumCde = TextBox1.Value
Requete = "select ((T2.'GEST' || TO_CHAR(TRUNC(T2.'NU_CDE' ))) || TO_CHAR(TRUNC(T2.'LIG_CDE'))), T2.'MT_ENGAGE' - T3.'MT_LIQ' , T3.'MT_LIQ' , T2.'MT_ENGAGE' , T1.'NU_LIQ' , T2.'QTE_CDEE' , T2.'LIBELLE_LIGNE_CDE' , T1.'LIG_CDE' , T1.'NU_CDE' , T1.'GEST' , T2.'QTE_RECUE' from ('LIG_COMMANDE' T2 LEFT OUTER JOIN ('RECEP_CDE' T3 LEFT OUTER JOIN 'MANDATS_DE_COMMANDES' T1 on T3.'EH'=T1.'EH' and T3.'GEST'=T1.'GEST' and T3.'NU_CDE'=T1.'NU_CDE' and T3.'LIG_CDE'=T1.'LIG_CDE' and T3.'NU_RECEP'=T1.'NU_RECEP' and T3.'NUM_LIQ'=T1.'NU_LIQ') on T2.'EH'=T3.'EH' and T2.'GEST'=T3.'GEST' and T2.'NU_CDE'=T3.'NU_CDE' and T2.'LIG_CDE'=T3.'LIG_CDE') where T1.'NU_CDE'=" & NumCde _
& " order by 'LIG_CDE' asc"
With ActiveWorkbook.Connections("IN01 (Par défaut) COMMANDE").OLEDBConnection
.BackgroundQuery = True
.CommandText = Requete
.CommandType = xlCmdSql
.Connection = "OLEDB;Provider=MSDAORA.1;User ID=MAG2;Data Source=IN01"
.RefreshOnFileOpen = False
.SavePassword = True
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.Refresh
End With
End Sub
Sub ConnecterBase(ConnectBD As ADODB.Connection, _
CheminBD As String, _
Optional Rs)
Set ConnectBD = New ADODB.Connection
If Not IsMissing(Rs) Then
Set Rs = New ADODB.Recordset
End If
With ConnectBD
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = CheminBD
.Open
End With
End Sub
Sub Requete(Req As String)
Dim ConnectBD As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim ChaineSQL As String
ChaineSQL = "select ((T2.'GEST' || TO_CHAR(TRUNC(T2.'NU_CDE' ))) || TO_CHAR(TRUNC(T2.'LIG_CDE'))), T2.'MT_ENGAGE' - T3.'MT_LIQ' , T3.'MT_LIQ' , T2.'MT_ENGAGE' , T1.'NU_LIQ' , T2.'QTE_CDEE' , T2.'LIBELLE_LIGNE_CDE' , T1.'LIG_CDE' , T1.'NU_CDE' , T1.'GEST' , T2.'QTE_RECUE' from ('LIG_COMMANDE' T2 LEFT OUTER JOIN ('RECEP_CDE' T3 LEFT OUTER JOIN 'MANDATS_DE_COMMANDES' T1 on T3.'EH'=T1.'EH' and T3.'GEST'=T1.'GEST' and T3.'NU_CDE'=T1.'NU_CDE' and T3.'LIG_CDE'=T1.'LIG_CDE' and T3.'NU_RECEP'=T1.'NU_RECEP' and T3.'NUM_LIQ'=T1.'NU_LIQ') on T2.'EH'=T3.'EH' and T2.'GEST'=T3.'GEST' and T2.'NU_CDE'=T3.'NU_CDE' and T2.'LIG_CDE'=T3.'LIG_CDE') where T1.'NU_CDE'=" & Req & " order by 'LIG_CDE' asc"
ConnecterBase ConnectBD, "\\CLR1DONNEES\0160xxxx\0160*****\My Documents\Mes sources de données\IN01 (Par défaut) COMMANDE.odc", Rs
With Rs
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open ChaineSQL, ConnectBD
Do Until .EOF
Debug.Print .Fields("Champ1") _
& ", " & .Fields("Champ2") _
& ", " & .Fields("Champ3")
.MoveNext
Loop
End With
ConnectBD.Close
Set ConnectBD = Nothing
Set Rs = Nothing
End Sub
Private Sub CommandButton1_Click()
Dim NumCde As String
NumCde = TextBox1.Value
Worksheets(1).Requete (NumCde)
End Sub