' Attend connexion terminée Do While (conn.State = adStateConnecting) DoEvents Loop ' Test la connexion If conn.State <> adStateOpen Then MsgBox "Pas de connexion" Exit Sub EndIf
rs.Open sqlstr, conn, adOpenStatic, adLockReadOnly
rs.Open sqlstr, conn, adOpenStatic, adLockReadOnly
rs.Open (sqlstr, conn, adOpenStatic, adLockReadOnly)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionSub mysql() Dim conn As New ADODB.Connection Set conn = New ADODB.Connection Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim server_name As String Dim database_name As String Dim user_id As String Dim password As String Dim sqlstr As String Dim vtype As Variant Set ws = Worksheets("data") Set cl = Worksheets("SLA") driver_name = "{Mysql ODBC 5.1 Driver}" server_name = "XXX.XXX.XXX.XXX" database_name = "DB" user_id = "USER" password = "PASS" conn.Open "DRIVER=" & driver_name _ & ";SERVER=" & server_name _ & ";DATABASE=" & database_name _ & ";UID=" & user_id _ & ";PWD=" & password _ & ";OPTION=16427" conn.CursorLocation = adUseClient vtype = Array("Text", "LongText", "Int(10)", "Float", "Double", "Date", "Time") sqlstr = "SELECT Z.*" sqlstr = sqlstr & ", dateTimeDiff(datedebutretenu,dateresolu, 9) as duree" sqlstr = sqlstr & ", dateTimeDiff(datedebutretenu,dateresolu, 1) as dureeho" sqlstr = sqlstr & ", dateTimeDiff(datedebutretenu,dateresolu, 5) as dureeso" sqlstr = sqlstr & ", ifnull(nattcli,0) as nattcli,ifnull(secattcli,0) as secattcli, ifnull(secoattcli,0) as secoattcli, ifnull(secsoattcli,0) as secsoattcli" sqlstr = sqlstr & ", ifnull(nattext,0) as nattext,ifnull(secattext,0) as secattext, ifnull(secoattext,0) as secoattext, ifnull(secsoattext,0) as secsoattext" sqlstr = sqlstr & ", ifnull(ntransit,0) as ntransit,ifnull(sectransit,0) as sectransit, ifnull(secotransit,0) as secotransit, ifnull(secsotransit,0) as secsotransit" sqlstr = sqlstr & " FROM (" sqlstr = sqlstr & " SELECT X.*" sqlstr sqlstr & ", CONVERT(IF(requestId 20482" sqlstr = sqlstr & ", IF (DatePrevue < DateCreation, dateresolu, DatePrevue)" sqlstr = sqlstr & ", DateCreation)" sqlstr = sqlstr & ", DATETIME) AS datedebutretenu" sqlstr sqlstr & ", CONVERT(IF(requestId 20482" sqlstr = sqlstr & ",IF (DatePrevue < DateCreation" sqlstr = sqlstr & ", IF(Status REGEXP '^(Clos|R.*solu)$', DateResolv, NOW())" sqlstr = sqlstr & ", IF(Status REGEXP '^(Clos|R.*solu)$' AND DatePrevue > DateResolv, DateResolv, DatePrevue))" sqlstr = sqlstr & ", DateCreation)" sqlstr = sqlstr & ", DATETIME) AS dategroupement" sqlstr = sqlstr & " FROM (" sqlstr = sqlstr & " SELECT t.* , CONVERT(ifnull(d.DateResolvForce,'1970-01-01 00:00:00'), DATETIME) as DateResolvForce" sqlstr = sqlstr & ", CONVERT(IF (Status REGEXP '^(Clos|R.*solu)$'" sqlstr = sqlstr & ", IF(YEAR(IFNULL(DateResolvForce,DateResolvEff)) > 1970" sqlstr = sqlstr & ", IFNULL(DateResolvForce,DateResolvEff)" sqlstr = sqlstr & ", IF (YEAR(DateResolv) > 1970, DateResolv, DateDernInterv))" sqlstr = sqlstr & ", NOW())" sqlstr = sqlstr & ",DATETIME) AS dateresolu" sqlstr = sqlstr & " FROM tickets t " sqlstr sqlstr & "JOIN mgd_Client C ON (C.id t.ClientId) " sqlstr sqlstr & "LEFT JOIN ForceDateResolv d ON (d.TicketId t.TicketId AND d.Systeme = t.Systeme) " sqlstr sqlstr & "where CodeClient " & cl.Cells(7, 11).Value sqlstr = sqlstr & " ) X" sqlstr = sqlstr & " ) Z" sqlstr sqlstr & " LEFT JOIN ticketsRecapAttente A ON (A.Systeme Z.Systeme AND A.TicketId = Z.TicketId AND ApresResol = 0);" rs.Open sqlstr, conn, adOpenStatic, adLockOptimistic With ws.Range("A2:BH65536") .CopyFromRecordset rs End With On Error Resume Next rst.Close Set rs = Nothing conn.Close Set conn = Nothing On Error GoTo 0 End Sub