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 |
