1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
| 'Extraction des mt repo
Private Sub Ext_mtrepo()
'Désactiver les alertes
DoCmd.SetWarnings False
'En cas d'erreur
On Error GoTo err
'ODBC control
If IsNull(zuid = DLookup("[UID]", "[SQL_PARAM]", "[USER]='" & iUser & "'")) = True Then
msg = MsgBox("Paramètres ODBC requis pour se connecter sur Lara.", vbCritical, "GPREV_MT_REPO")
DoCmd.SetWarnings True
Me.zTEC = "ERREUR"
Exit Sub
Else
zuid = DLookup("[UID]", "[SQL_PARAM]", "[USER]='" & iUser & "'")
zPwd = DLookup("[PWD]", "[SQL_PARAM]", "[USER]='" & iUser & "'")
zserver = DLookup("[SERVER]", "[SQL_PARAM]", "[USER]='" & iUser & "'")
End If
'Variables
Dim db As Database
Dim rs As Recordset
Dim strsql As Variant
Dim MaBase As Database
Dim MaReq As QueryDef
Dim MonRes As Recordset
Dim c As Long
Me.eMtRepo.BackColor = RGB(236, 189, 66)
Me.Repaint
DoEvents
Set db = CurrentDb
Set MaBase = DBEngine(0)(0)
'Suppression de l'ancienne requête si trouvée
If ObjectExists("Query", "SQL_MTREPO_DEF") Then
DoCmd.DeleteObject acQuery, "SQL_MTREPO_DEF"
End If
Set MaReq = MaBase.CreateQueryDef("SQL_MTREPO_DEF")
'Connexion
MaReq.Connect = "ODBC;DRIVER=Microsoft ODBC for Oracle;UID= " & zuid & ";PWD=" & zPwd & ";SERVER=" & zserver
'Construction chaine SQL d'interrogation
If ObjectExists("Table", "T_MTREPO_TMP") Then
db.Execute "delete * from T_MTREPO_TMP"
End If
'Chainage de l'instruction SQL
Set rs = db.OpenRecordset("SQL_MTREPO")
rs.MoveFirst
Do While Not rs.EOF
strsql = strsql & rs!sql_line & " "
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
MaReq.SQL = strsql
'Interrogation de LARA et récupération des données
zdint = VBA.DateTime.Timer
MaReq.ReturnsRecords = True
DoCmd.OpenQuery "CREA T_MTREPO_TMP"
zfsec = Int(VBA.DateTime.Timer - zdint)
zfmin = Int(zfsec / 60)
zfint = CStr(zfmin) & "m" & CStr(zfsec - (zfmin * 60)) & "s"
db.Execute ("delete * from T_PERFORMANCE where SQL_OBJET = 'MT REPO' and SQL_DATE= #" & Format(Date, "MM/DD/YYYY") & "#")
db.Execute "insert into T_PERFORMANCE (SQL_GROUPE,SQL_DATE,SQL_DUREE,SQL_OBJET)" & _
"values ('GPREV',#" & Format(Date, "MM/DD/YYYY") & "#,'" & zfint & "','MT REPO')"
'Fermeture
MaBase.Close
Set MaReq = Nothing
Set MonRes = Nothing
Set MaBase = Nothing
Me.eMtRepo.BackColor = vbGreen
Me.Repaint
DoEvents
'Fin
DoCmd.SetWarnings True
Exit Sub
'Message d 'erreur
err:
info = MsgBox(err.Description, vbCritical, "GPREV_MT_REPO")
iError = True
Me.zTEC = "ERREUR"
DoCmd.SetWarnings True
End Sub |
Partager