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
|
Public Sub test_iteration()
Dim Mba As Database, TabTempo As DAO.Recordset, NumId As Variant
Dim rs As DAO.Recordset
Dim MAX_NUM As Integer 'Id de la table LegalEntity
Set Mba = CurrentDb()
Set TabTempo = Mba.OpenRecordset("Temporary_Table")
compteur = 0
Do Until TabTempo.EOF
DoCmd.SetWarnings False
'insertion dans la LegalEntity l'enregistement en cour de TabTempo
Sql1 = "INSERT INTO LegalEntity(Country, unit, plant) Values ('" & TabTempo("Country") & "', '" & TabTempo("unit") & "', '" & TabTempo("plant") & "')"
DoCmd.RunSQL Sql1
Set rs = CurrentDb.OpenRecordset("Select MAX(idNexans)as MAX_NUM From LegalEntity", dbOpenForwardOnly, dbReadOnly)
MAX_NUM = rs.Fields("MAX_NUM").Value
rs.Close
Sql2 = "INSERT INTO Suppliers(name, [contact name], adresse, [e-mail OR telephone], [EU / non EU], idNexans) Values ('" & TabTempo("Name") & "', '" & TabTempo("[contact name]") & "', '" & TabTempo("Address") & "', '" & TabTempo("[e-mail OR telephone]") & "', '" & TabTempo("[EU / non EU]") & "', '" & MAX_NUM & "')"
DoCmd.RunSQL Sql2
'Sql3 = "INSERT INTO Products(productName, chemicalName, usedForAs, casNumber, elincsEinecs, amountUsed, hazardousSubstancesUsed) Values ('" & TabTempo("[Product name]") & "', '" & TabTempo("[chemical name]") & "', '" & TabTempo("[used for / as1)]") & "', '" & TabTempo("[CAS number]") & "', '" & TabTempo("[ELINCS / EINECS]") & "', '" & TabTempo("[Forecast 2008 in KG / amount used / a *]") & "', '" & TabTempo("[hazardous substances used ?]") & "')"
'DoCmd.RunSQL Sql3
TabTempo.MoveNext
Loop
End Sub |
Partager