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 100 101 102 103 104 105 106 107 108 109
| Sub netbackup(BDServer, BDName, BDUid, BDPassword)
Dim nbmot As Integer
Dim nummot As Integer
Dim i As Integer
Dim lettreprecedent As Boolean
Dim contenu As String
Dim lettre As String
On Error Resume Next
Set objCnx = CreateObject("ADODB.Connection")
objCnx.Open "driver={SQL Server};server=" & BDServer & ";Language=Français;Regional=Yes;database=" & BDName & ";uid=" & BDUid & ";pwd=" & BDPassword
If (Err.Number) Then
MsgBox ("Serveur " & BDServer & " la base " & BDName & " n'existe pas.")
strVersionCnet = ""
Err.Clear
Else
Set objCmd = CreateObject("ADODB.Command")
If (Err.Number) Then
MsgBox ("Impossible de connecter la base ADODB.Command " & Err.Description)
End If
Set objCmd.ActiveConnection = objCnx
If (Err.Number) Then
MsgBox ("objCmd.ActiveConnection " & Err.Description)
End If
objCmd.CommandType = 1
Set rSetLecture = CreateObject("ADODB.RecordSet")
strRequete = "SELECT DISTINCT NomSrv FROM NETBACKUP_SERVEUR "
strRequete2 = "DELETE FROM NETBACKUP_GERER WHERE NomSrv='" & Cells(i, 1) & " '"
strRequete3 = "DELETE FROM NETBACKUP_APPARTENIR WHERE NomSrv='" & Cells(i, 1) & " '"
strRequete4 = "INSERT INTO NETBACKUP_GERER(NomSrv,emailResp) VALUES ('" & Cells(i, 1) & "','" & Cells(i, 7) & "')"
strRequete5 = "INSERT INTO NETBACKUP_APPARTENIR(NomSrv,NomFonction) VALUES ('" & Cells(i, 1) & "','" & Cells(i, 3) & "')"
strRequete6 = "INSERT INTO NETBACKUP_SERVEUR(Nom,Commentaire) VALUES ('" & Cells(i, 1) & " ',NULL)"
objCmd.CommandText = strRequete
objCmd.CommandText = strRequete2
objCmd.CommandText = strRequete3
objCmd.CommandText = strRequete4
objCmd.CommandText = strRequete5
objCmd.CommandText = strRequete6
rSetLecture.Open objCmd, , 0, 1
i = 6
Set Db = currentdb
Do While Cells(i, 1) <> ""
If Db.Execute(strRequete) = Cells(i, 1) Then
Db.Execute ("DELETE FROM NETBACKUP_GERER WHERE NomSrv='" & Cells(i, 1) & " '")
MsgBox (Cells(i, 1))
nbmot = Len(Cells(i, 3))
nummot = 1
lettreprecedent = True
contenu = Range("C" & i)
Range("C" & i) = ""
Do While nummot <> (nbmot + 1)
lettre = Mid(contenu, nummot, 1)
If lettre <> " " Then
Cells(i, 3) = Cells(i, 3) & lettre
lettreprecedent = True
Else
If lettreprecedent Then
Db.Execute "INSERT INTO NETBACKUP_APPARTENIR(NomSrv,NomFonction) VALUES ('" & Cells(i, 1) & "','" & Cells(i, 3) & "')"
Cells(i, 3) = Cells(i, 3) & " "
lettreprecedent = False
End If
End If
nummot = nummot + 1
Loop
Else
Db.Execute "INSERT INTO NETBACKUP_SERVEUR(Nom,Commentaire) VALUES ('" & Cells(i, 1) & " ',NULL)"
nbmot = Len(Cells(i, 3))
nummot = 1
lettreprecedent = True
contenu = Range("C" & i)
Range("C" & i) = ""
Do While nummot <> (nbmot + 1)
lettre = Mid(contenu, nummot, 1)
If lettre <> " " Then
Cells(i, 3) = Cells(i, 3) & lettre
lettreprecedent = True
Else
If lettreprecedent Then
Db.Execute "INSERT INTO NETBACKUP_APPARTENIR(NomSrv,NomFonction) VALUES ('" & Cells(i, 1) & "','" & Cells(i, 3) & "')"
Cells(i, 3) = Cells(i, 3) & " "
lettreprecedent = False
End If
End If
nummot = nummot + 1
Loop
End If
i = i + 1
Loop
MsgBox ("fin connexion bdd")
rSetLecture.Close
End If
Set objCnx = Nothing
End Sub |
Partager