Option Explicit On 

Module MySQL
    Public Server As String = "Adresse IP"
    Public User As String = "Utilisateur"
    Public Pwd As String = "Mot de Passe"
    Public Connexion As ADODB.Connection
    Public Rs As ADODB.Recordset
    Public DataId As Integer


    Public Function MySQL_Conn(ByVal Db As String) As ADODB.Connection

        'Connect to database
        MySQL_Conn = New ADODB.Connection()
        MySQL_Conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                                    & "SERVER=" & Server & ";" _
                                    & "DATABASE=" & Db & ";" _
                                    & "UID=" & User & ";" _
                                    & "PWD=" & Pwd & ";" _
                                    & "OPTION=3"


        MySQL_Conn.Open()

    End Function

    Public Sub MySQL_NewDb(ByRef MySQL_Conn As ADODB.Connection, ByVal Db_Name As String)

        'New database
        MySQL_Conn.Execute("DROP DATABASE IF EXISTS " & Db_Name)
        MySQL_Conn.Execute("CREATE DATABASE " & Db_Name)

    End Sub

    Public Sub MySQL_NewTable(ByRef MySQL_Conn As ADODB.Connection, ByVal TableName As String, ByVal Attrib As String)

        'New table
        MySQL_Conn.Execute("DROP TABLE IF EXISTS " & TableName)
        MySQL_Conn.Execute("CREATE TABLE " & TableName & "(" & Attrib & ")")

    End Sub

    Public Function MySQL_Select(ByRef MySQL_Conn As ADODB.Connection, ByVal Fields As String, ByVal Table As String, Optional ByVal Where As String = "", Optional ByVal Group As String = "", Optional ByVal Order As String = "") As ADODB.Recordset

        Dim strSQL As String

        'Select string
        strSQL = "SELECT " & Fields & " FROM `" & Table & "`"
        If Where <> "" Then strSQL = strSQL & " WHERE " & Where
        If Group <> "" Then strSQL = strSQL & " GROUP BY " & Group
        If Order <> "" Then strSQL = strSQL & " ORDER BY " & Order
        MySQL_Select = New ADODB.Recordset()
        MySQL_Select.CursorLocation = ADODB.CursorLocationEnum.adUseServer
        MySQL_Select.Open(strSQL, MySQL_Conn)

    End Function

    Public Function MySQL_SelectR(ByRef MySQL_Conn As ADODB.Connection, ByVal strSQL As String) As ADODB.Recordset

        'Select string
        MySQL_SelectR = New ADODB.Recordset()
        MySQL_SelectR.CursorLocation = ADODB.CursorLocationEnum.adUseServer
        MySQL_SelectR.Open(strSQL, MySQL_Conn)

    End Function

    Public Sub MySQL_Insert(ByRef MySQL_Conn As ADODB.Connection, ByVal Table As String, ByVal Fields As String, ByVal Values As String)

        'Insert data
        MySQL_Conn.Execute("INSERT INTO `" & Table & "` (" & Fields & ") VALUES (" & Values & ")")

    End Sub

    Public Sub MySQL_Delete(ByRef MySQL_Conn As ADODB.Connection, ByVal Table As String, ByVal WhereField As String, ByVal WhereValue As String)

        'Delete data
        MySQL_Conn.Execute("DELETE FROM `" & Table & "` WHERE " & WhereField & " =" & WhereValue)

    End Sub

    Public Sub MySQL_Update(ByRef MySQL_Conn As ADODB.Connection, ByVal Table As String, ByVal Fields As String, ByVal WhereFields As String)

        'Update data
        MySQL_Conn.Execute("UPDATE `" & Table & "` SET " & Fields & " WHERE " & WhereFields)

    End Sub

    Public Sub Rs_Close()
        'Close recordset
        If Not (Rs Is Nothing) Then
            If Rs.State = 1 Then Rs.Close()
        End If
    End Sub
End Module