Compléter une clef étrangère sql
n
Bonjour j'ai un soucis, je travail sur excel vba. je créer une table et la remplie via du SQL
j'aimerai lier ma table Liste salarié a la nouvelle table que je créer.
Est-ce que je dois intégrer l'ID de la table que je créer (=clef E) a la table excel que je met sous SQL dans se cas dois-je passer par l'extraction de donnée access vers excel?
ou je peux faire une jointure de mes deux tables et dire " where nom_prénom table1 =nom-prénom table deux" et je recopie ID de table 1 sur ID table 2? (c'est la que je bloque je ne sais pas recopier ID table 1 vers ID table 2)
J'espere être assez précis... ci-joint mon code.
mes variables cnx etc sont des variables public que j'utilise dans d'autre module
Cordialement,
Naubin
Sub creertable()
Dim semaine%, num_colonne%, str$
Dim ladate As Date
ladate = Date
semaine = Format(ladate, "ww", vbMonday, vbFirstFourDays)
'On Error GoTo lastline:
'Définition du pilote de connexion en fonction de la version office
cnx.Provider = "Microsoft.ACE.OLEDB.12.0"
'Définition de la chaîne de connexion
cnx.ConnectionString = "monchemin"
'Ouverture de la base de données
cnx.Open
cnx.Execute "DROP TABLE S" & semaine
cnx.Execute "CREATE TABLE [S" & semaine & "](ID integer not null," & _
"Nom_Prenom VARCHAR)" '& _
" FOREIGN KEY(ID) REFERENCES liste_salarie(identifiant_salarie))"
With Sheets("Pointage")
cnx.Execute "ALTER TABLE [S" & semaine & "]" & _
"ADD COLUMN Absences DATETIME," & _
" MIB_XFA DATETIME," & _
" M5K0 DATETIME," & _
" M6 DATETIME," & _
" BER DATETIME," & _
" DELTA DATETIME," & _
" 10T DATETIME," & _
" MEYER1FINITION DATETIME," & _
" PM3 DATETIME," & _
" MEYER2 DATETIME," & _
" AlpiJKT DATETIME," & _
" C10_Tondeuse DATETIME," & _
" C16 DATETIME," & _
" C17 DATETIME," & _
" C21 DATETIME," & _
" TOYOTA DATETIME," & _
" DEC10 DATETIME," & _
" GRAHER DATETIME," & _
" Thermocompression DATETIME," & _
" Qualite DATETIME," & _
" TriQualite_Reclamation_Client DATETIME," & _
" TriQualite_facture_fournisseur DATETIME," & _
" RemplacementSUP DATETIME," & _
" VisiteMedical DATETIME"
cnx.Execute "ALTER TABLE [S" & semaine & "]" & _
" ADD COLUMN Logistique DATETIME," & _
" AiguillePRC6 DATETIME," & _
" Cariste DATETIME," & _
" Formation_ligne DATETIME," & _
" Formation_org_ext DATETIME," & _
" Formation_sprint DATETIME," & _
" Essais DATETIME," & _
" Reunion DATETIME," & _
" Industrialisation DATETIME," & _
" Bondesortie DATETIME," & _
" Délégation DATETIME"
For x = 30 To Range("WZ30").End(xlDown).Row - 1
str = "INSERT INTO S" & semaine & _
" VALUES(" & .Cells(x, 624) & "','" & .Cells(x, 625) & "','" & .Cells(x, 626) & "','" & _
Cells(x, 627) & "','" & Cells(x, 628) & "','" & Cells(x, 629) & "','" & _
Cells(x, 630) & "','" & Cells(x, 631) & "','" & Cells(x, 632) & "','" & _
Cells(x, 633) & "','" & Cells(x, 634) & "','" & Cells(x, 635) & "','" & _
Cells(x, 636) & "','" & Cells(x, 637) & "','" & Cells(x, 638) & "','" & _
Cells(x, 639) & "','" & Cells(x, 640) & "','" & Cells(x, 641) & "','" & _
Cells(x, 642) & "','" & Cells(x, 643) & "','" & Cells(x, 644) & "','" & _
Cells(x, 645) & "','" & Cells(x, 646) & "','" & Cells(x, 647) & "','" & _
Cells(x, 648) & "','" & Cells(x, 649) & "','" & Cells(x, 650) & "','" & _
Cells(x, 651) & "','" & Cells(x, 652) & "','" & Cells(x, 653) & "','" & _
Cells(x, 654) & "','" & Cells(x, 655) & "','" & Cells(x, 656) & "','" & _
Cells(x, 657) & "','" & Cells(x, 658) & "','" & Cells(x, 659) & "')"
Next
cnx.Execute str, dbFailOnerror
End With
cnx.Execute "DROP TABLE S" & semaine
Set cnx = Nothing
Exit Sub
lastline:
cnx.Execute "DROP TABLE S" & semaine
Set cnx = Nothing
End Sub