Attribute VB_Name = "Moduleprincipale"
Dim pilot As New ADODB.Connection
Option Explicit
Public Sub Etablir_Connection()
pilot.Provider = "Microsoft.Jet.OLEDB.4.0"
pilot.ConnectionString = "C:\Dossier Pilote\pilot.mdb"
pilot.Open
End Sub
Public Sub Fermer_Connection()
pilot.Close
End Sub
Public Sub ajoutpil(ByVal npil As Integer, ByVal no As String, ByVal pre As String, ByVal te As Long, _
ByVal dv As Integer, ByVal ma As String, ByVal ad As String, ByVal nli As Long, ByVal datnai As String, _
ByVal nat As String, ByVal datob As Date, ByVal autlic As String, ByVal qualradio As String, ByVal autli_qu As String, _
ByVal tepr As Long, ByVal numpass As String, ByVal lideliv As String, ByVal datdeliv As Date, ByVal datexp As Date)
    'Procédure permettant d'inserrer dans les tables tous les renseignements
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim Pilote As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Insert into pilote Values(" & npil & ",'" & no & "','" & pre & "'," & te & "," & dv & ",'" & ma & "','" & ad & "'," & nli & ",'" & datnai & "','" & nat & "','" & numpass & "',#" & CStr(datob) & "#,'" & autlic & "','" & qualradio & "','" & autli_qu & "'," & tepr & ",'" & lideliv & "',#" & CStr(datdeliv) & "#,#" & CStr(datexp) & "#)"
 Set Pilote = Reqsql.Execute
 Call Fermer_Connection
End Sub

Public Sub ajoutrally(ByVal npil As Integer, ByVal nral As String, ByVal no As String, ByVal dral As String, ByVal dur As String, _
ByVal dis As String, ByVal nbp As String, ByVal de As String, ByVal sa As String, ByVal py As String)
    'Procédure permettant d'inserrer dans les tables tous les renseignements

Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim rallye As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Insert into rallye Values(" & npil & ",'" & nral & "','" & no & "','" & dral & "','" & dur & "','" & dis & "','" & nbp & "','" & de & "','" & sa & "','" & py & "')"
Set rallye = Reqsql.Execute
Call Fermer_Connection
End Sub
Public Sub ajoutpossed(ByVal npil As Integer, ByVal no As String, ByVal Nav As String, ByVal ndv As Integer, ByVal dms As Date, ByVal typ As String, ByVal md As String, _
ByVal carb As String, ByVal typuiss As String, ByVal prop As String)
    'Procédure permettant d'inserrer dans les tables tous les renseignements

Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim posseder As New ADODB.Recordset


Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Insert into posseder Values(" & npil & ",'" & no & "','" & Nav & "'," & ndv & ",#" & CStr(dms) & "#,'" & md & "','" & carb & "','" & typuiss & "','" & prop & "')"
Set posseder = Reqsql.Execute
 Call Fermer_Connection
End Sub

Public Sub ajoutresult(ByVal npil As Integer, ByVal Res As String, ByVal nral As String, ByVal no As String)
    'Procédure permettant d'inserrer dans les tables tous les renseignements
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim résultat As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Insert into résultat Values(" & npil & ",'" & Res & "','" & nral & "','" & no & "')"
Set résultat = Reqsql.Execute
 Call Fermer_Connection
End Sub
Public Sub idnom(ByVal npil As Integer)

Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim Res As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Select N°pilote From pilote Where N°pilote =  " & npil & ""
Set Res = Reqsql.Execute
If Not Res.EOF = False Then
    MsgBox "Ce N°pilote n'existe pas, Veuillez en Choisir un autre ", vbInformation, "Information"
    End If
Call Fermer_Connection
End Sub
Public Sub affichpil(ByVal npil As Integer, ByRef no As String, ByRef pre As String, ByRef te As Long, _
ByRef dv As Integer, ByRef ma As String, ByRef ad As String, ByRef nli As Long, ByRef datnai As String, _
ByRef nat As String, ByRef datob As Date, ByRef autlic As String, ByRef qualradio As String, ByRef autli_qu As String, _
ByRef tepr As Long, ByRef numpass As String, ByRef lideliv As String, ByRef datdeliv As Date, ByRef datexp As Date)
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim Pilote As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Select *  From pilote Where N°pilote = " & npil & ""
Set Pilote = Reqsql.Execute
        'permet de savoir le nom et le N°Licence qu'on cherche, existe dans la BD
    If Not Pilote.EOF = False Then
    Call Fermer_Connection
    Exit Sub
   End If
npil = Pilote![N°pilote]: no = Pilote![nom_pilote]: pre = Pilote![Prénom]: te = Pilote![tel]: dv = Pilote![Heures_de_vol]
ma = Pilote![e_mail]: ad = Pilote![Adresse]: nli = Pilote![N°Licence]: datnai = Pilote![date_de_naissance]: nat = Pilote![Nationalité]
numpass = Pilote![Numéro_passeport]: datob = Pilote![Date_d_obtention]: autlic = Pilote![autres_licence]
qualradio = Pilote![qualification_radio]: autli_qu = Pilote![Autres_licence_ou_qualification]: tepr = Pilote![Tel_professionnel]
lideliv = Pilote![lieu_delivrance]: datdeliv = Pilote![date_delivrance]: datexp = Pilote![date_expiration]
Call Fermer_Connection
End Sub
Public Sub affichral(ByVal npil As Integer, ByRef nral As String, ByRef dral As String, ByRef dur As String, _
ByRef dis As String, ByRef nbp As String, ByRef de As String, ByRef sa As String, ByRef py As String)
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim rallye As New ADODB.Recordset

Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Select * From rallye Where N°pilote = " & npil & ""
Set rallye = Reqsql.Execute
        'permet de savoir, si le nom et le N°Licence qu'on cherche, existe dans la BD
    If Not rallye.EOF = False Then
    Call Fermer_Connection
    Exit Sub
    End If
npil = rallye![N°pilote]
nral = rallye![nom_rallye]
dral = rallye![date_de_rallye]
dur = rallye![durée]
dis = rallye![distance]
nbp = rallye![n°participant]
de = rallye![nom_site_décollage]
sa = rallye![nom_site_attérissage]
py = rallye![paye]
Call Fermer_Connection
End Sub

Public Sub affichpossed(ByVal npil As Integer, ByRef Nav As String, ByRef ndv As Integer, ByRef dms As Date, ByRef typ As String, ByRef md As String, _
ByRef carb As String, ByRef typuiss As String, ByRef prop As String)
    'Procédure permettant d'inserrer dans les tables tous les renseignements

Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim posseder As New ADODB.Recordset

Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Select * From posseder Where  N°pilote = " & npil & ""
    Set posseder = Reqsql.Execute
        'permet de savoir, si le nom et le N°Licence qu'on cherche, existe dans la BD
    
    If Not posseder.EOF = False Then
    Call Fermer_Connection
    Exit Sub
    End If
    npil = posseder![N°pilote]: Nav = posseder![N°avion]: ndv = posseder![Nombre_heures_vol]: dms = posseder![Date_de_mise_en_service]: md = posseder![modèle]: carb = posseder![Carburant]
typuiss = posseder![type_ou_puissance_de_moteur]: prop = posseder![propriété_ou_exploitant]
Call Fermer_Connection
End Sub
Public Sub affichresult(ByVal npil As Integer, ByRef Res As String)
    'Procédure permettant d'inserrer dans les tables tous les renseignements
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim résultat As New ADODB.Recordset


Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Select * From résultat Where N°pilote = " & npil & ""
Set résultat = Reqsql.Execute

    If Not résultat.EOF = False Then
    Call Fermer_Connection
    Exit Sub
    End If
npil = résultat![N°pilote]
Res = résultat![classement]

 Call Fermer_Connection
End Sub
Public Sub suppil(ByVal npil As Integer, ByVal no As String, ByVal pre As String, ByVal te As Long, _
ByVal dv As Integer, ByVal ma As String, ByVal ad As String, ByVal nli As Long, ByVal datnai As String, _
ByVal nat As String, ByVal datob As Date, ByVal autlic As String, ByVal qualradio As String, ByVal autli_qu As String, _
ByVal tepr As Long, ByVal numpass As String, ByVal lideliv As String, ByVal datdeliv As Date, ByVal datexp As Date)
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim Pilote As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Delete From pilote Where  nom_pilote = '" & no & "' and N°pilote = " & npil & ""
 Set Pilote = Reqsql.Execute
 Call Fermer_Connection

End Sub
Public Sub supral(ByVal npil As Integer, ByVal nral As String, ByVal no As String, ByVal dral As String, ByVal dur As String, _
ByVal dis As String, ByVal nbp As String, ByVal de As String, ByVal sa As String, ByVal py As String)

Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim rallye As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Delete From rallye Where N°pilote = " & npil & ""
Set rallye = Reqsql.Execute
Call Fermer_Connection

End Sub
Public Sub suppossed(ByVal npil As Integer, ByVal no As String, ByVal Nav As String, ByVal ndv As Integer, ByVal dms As Date, ByVal typ As String, ByVal md As String, _
ByVal carb As String, ByVal typuiss As String, ByVal prop As String)

Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim posseder As New ADODB.Recordset

Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Delete From posseder Where N°pilote = " & npil & ""
Set posseder = Reqsql.Execute
 Call Fermer_Connection
End Sub
Public Sub supresult(ByVal npil As Integer, ByVal Res As String, ByVal nral As String, ByVal no As String)

Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim résultat As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "Delete From résultat Where N°pilote = " & npil & ""
Set résultat = Reqsql.Execute
 Call Fermer_Connection
End Sub
Public Sub majpil(ByVal npil As Integer, ByVal no As String, ByVal pre As String, ByVal te As Long, _
ByVal dv As Integer, ByVal ma As String, ByVal ad As String, ByVal nli As Long, ByVal datnai As String, _
ByVal nat As String, ByVal datob As Date, ByVal autlic As String, ByVal qualradio As String, ByVal autli_qu As String, _
ByVal tepr As Long, ByVal numpass As String, ByVal lideliv As String, ByVal datdeliv As Date, ByVal datexp As Date)
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim Pilote As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "UPDATE pilote Set nom_pilote = '" & no & "', prénom = '" & pre & "', tel = " & te & ", heures_de_vol = " & dv & ", e_mail = '" & ma & "', adresse = '" & ad & "', N°Licence = " & nli & ", date_de_naissance = '" & datnai & "', nationalité = '" & nat & "', numéro_passeport = '" & numpass & "', date_d_obtention = #" & CStr(datob) & "#, autres_licence = '" & autlic & "', qualification_radio = '" & qualradio & "', autres_licence_ou_qualification = '" & autli_qu & "', tel_professionnel = " & tepr & ", lieu_delivrance = '" & lideliv & "', date_delivrance = #" & CStr(datdeliv) & "#, date_expiration = #" & CStr(datexp) & "#  Where pilote.[N°pilote] = " & npil & ""
 Set Pilote = Reqsql.Execute
 Call Fermer_Connection

End Sub
Public Sub majral(ByVal npil As Integer, ByVal nral As String, ByVal no As String, ByVal dral As String, ByVal dur As String, _
ByVal dis As String, ByVal nbp As String, ByVal de As String, ByVal sa As String, ByVal py As String)
    'Procédure permettant de faire la mise à jour des tables
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim rallye As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "UPDATE rallye Set N°pilote = " & npil & ", nom_rallye = '" & nral & "', nom_pilote = '" & no & "', date_de_rallye = '" & dral & "', durée = '" & dur & "', distance = '" & dis & "', n°participant = '" & nbp & "', nom_site_décollage = '" & de & "', nom_site_attérissage = '" & sa & "', paye = '" & py & "' Where rallye.[N°pilote] = " & npil & ""
Set rallye = Reqsql.Execute
Call Fermer_Connection
End Sub
Public Sub majpossed(ByVal npil As String, ByVal no As String, ByVal Nav As String, ByVal ndv As Integer, ByVal dms As Date, ByVal md As String, _
ByVal carb As String, ByVal typuiss As String, ByVal prop As String)
    'Procédure permettant de faire la mise à jour des tables
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim posseder As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "UPDATE posseder Set N°avion = '" & Nav & "', nombre_heures_vol = " & ndv & ", date_de_mise_en_service = #" & CStr(dms) & "#, modèle = '" & md & "', carburant = '" & carb & "', type_ou_puissance_de_moteur = '" & typuiss & "', propriété_ou_exploitant = '" & prop & "'  Where posseder.[N°pilote] = " & npil & ""
Set posseder = Reqsql.Execute
 Call Fermer_Connection
End Sub
Public Sub majres(ByVal npil As Integer, ByVal Res As String, ByVal nral As String, ByVal no As String)
    'Procédure permettant de faire la mise à jour des tables
Call Etablir_Connection

Dim Reqsql As New ADODB.Command
Dim résultat As New ADODB.Recordset
Reqsql.ActiveConnection = pilot
Reqsql.CommandText = "UPDATE résultat Set N°pilote = " & npil & ", classement = '" & Res & "', nom_pilote = '" & no & "', nom_rallye = '" & nral & "' Where résultat.[N°pilote] = " & npil & ""
Set résultat = Reqsql.Execute
 Call Fermer_Connection
End Sub

Public Function Identificat(ByVal MP As String, ByVal id As String) As Boolean
    Call Etablir_Connection
    Dim Reqsql As New ADODB.Command
    Dim identifi As New ADODB.Recordset
    Reqsql.ActiveConnection = pilot
Reqsql.CommandText = " Select * From identifi Where [Mot_de_passe] = '" & MP & "' And [Identification] = '" & id & "'"
Set identifi = Reqsql.Execute
If Not identifi.EOF = False Then
   MsgBox "Mot de Passe ou Identification Erroné", vbOKOnly, "Information"
frmSplash.Show
Else
   frmSplash.Hide
   Formrépert.Show
   End If
Call Fermer_Connection
End Function

