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 110
|
Sub importMSA2()
Dim rstEmploye As ADODB.Recordset
Dim rstSalaire As ADODB.Recordset
Dim rstCotisation As ADODB.Recordset
Dim CType As ADODB.CursorTypeEnum
Dim LType As ADODB.LockTypeEnum
Dim CLocation As ADODB.CursorLocationEnum
Dim Cnct As ADODB.Connection
Dim IDEmploye As Integer
Dim nomFichierMSA As String
Dim numFichier As Integer
Dim vNum As Integer
Dim id As Integer
Dim sql As String
Set rstEmploye = New ADODB.Recordset
With rstEmploye
CType = adOpenStatic
LType = adLockOptimistic
CLocation = adUseClient
.CursorLocation = CLocation
.CursorType = CType
.LockType = LType
Set .ActiveConnection = Cnct 'instance de ADODB.Connection
.Open "INSERT INTO Employes (numSS, nom) VALUES ('" & Mid(vLigne, 39, 13) & "', '" & Mid(vLigne, 62, 25) & "')" 'ta requête sql, par exemple, sql="select * from employés"
End With
Set rstSalaire = New ADODB.Recordset
With rstSalaire
CType = adOpenStatic
LType = adLockOptimistic
CLocation = adUseClient
.CursorLocation = CLocation
.CursorType = CType
.LockType = LType
Set .ActiveConnection = Cnct ' instance de ADODB.Connection
.Open "SELECT idEmploye FROM Employe WHERE " 'Requête sql
End With
Set rstCotisation = New ADODB.Recordset
With rstCotisation
CType = adOpenStatic
LType = adLockOptimistic
CLocation = adUseClient
.CursorLocation = CLocation
.CursorType = CType
.LockType = LType
Set .ActiveConnection = Cnct
.Open "INSERT INTO Cotisations (typeCotisation, montant) VALUES ('" & Mid(vLigne, 62, 5) & "','" & Mid(vLigne, 87, 12) & "')"
End With
nomFichierMSA = tbMSA.Text 'chemin du fichier .txt
numFichier = FreeFile 'Génère un descripteur de fichier libre, Définit un numéro de fichier libre
Open nomFichierMSA For Input As numFichier 'Ouvre le fichier (en mode lecture)
'vNum = 1
While Not EOF(numFichier)
Line Input #numFichier, vLigne 'Lit une ligne
Set Cnct = New ADODB.Connection
Cnct.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & tbBDD.Text & ";"
Cnct.Open
If Mid(vLigne, 60, 2) = "10" Then
vNomPrenom = Mid(vLigne, 62, 25)
vNumSS = Mid(vLigne, 39, 13)
MsgBox numSS & " " & nomPrenom
With rstEmploye
.AddNew
.Fields("numSS").Value = Mid(vLigne, 39, 13)
.Fields("nom").Value = Mid(vLigne, 62, 25)
.Update
IDEmploye = .Fields("idEmploye").Value
End With
End If
If Mid(vLigne, 60, 2) = "20" Then
brutMSA = Mid(vLigne, 88, 11)
vDate = Mid(vLigne, 52, 8)
MsgBox vDate & " " & brutMSA
With rstSalaire
.AddNew
.fields("idEmploye").value=...
.Fields("dateSalaire").Value = Mid(vLigne, 52, 8)
.Fields("brutMSA").Value = Mid(vLigne, 88, 11)
.Update
IDSalaire = .Fields("idSalaire").Value
End With
End If
If Mid(vLigne, 60, 2) = "30" Then
codeTypeCotisation = Mid(vLigne, 62, 5)
montant = Mid(vLigne, 87, 12)
MsgBox codeTypeCotisation & " " & montant
With rstCotisation
.AddNew
.Fields("typeCotisation").Value = Mid(vLigne, 62, 5)
.Fields("montant").Value = Mid(vLigne, 87, 12)
.Update
End With
End If
Wend
rstEmploye.Close
rstSalaire.Close
rstCotisation.Close
Cnct.Close
Set Cnct = Nothing 'Pour être sur que la connexion est bien fermé
Set rstEmploye = Nothing
Set rstSalaire = Nothing
Set rstCotisation = Nothing
End Sub |
Partager