Option Explicit


' ***********************************************************************
' *****                                                             *****
' *****        CODE PierreP56 : http://tatiak.canalblog.com/        *****
' *****                                                             *****
' ***********************************************************************


Public Cnx As Object, Rst As Object


Sub Connect_MySql(Optional x As Byte)
    Set Cnx = CreateObject("ADODB.Connection")
    Cnx.Provider = "MSDASQL"
    Cnx.Open "DRIVER={MySQL ODBC 5.1 Driver}; " & _
             "SERVER= adresse_serveur; DATABASE= nom_de_la_base; " & _
             "USER= identifiant; PASSWORD= mot_de_passe; " & _
             "Option=3"
    Set Rst = CreateObject("ADODB.Recordset")
End Sub


Sub Close_Cnx(Optional x As Byte)
    On Error Resume Next
    If x > 0 Then Rst.Close
    Cnx.Close
    Set Cnx = Nothing
    Set Rst = Nothing
End Sub


Function Select_Db(Req As String, Optional Head As Byte = 1) As Variant
Dim T As Variant, Rcd As Variant
Dim lig As Long, col As Long, i As Long, j As Long

    On Error GoTo errhdlr
    ReDim Rcd(1 To 1, 1 To 1)
    Rst.Open Req, Cnx, 3
    lig = Rst.RecordCount
    If lig > 0 Then
        Rst.MoveFirst
        T = Rst.GetRows
        col = Rst.Fields.Count
        ReDim Rcd(1 To lig + Head, 1 To col)
        
        For j = 0 To col - 1
            Rcd(1, j + 1) = Rst.Fields(j).Name
            For i = 0 To lig - 1
                Rcd(i + 1 + Head, j + 1) = IIf(IsNull(T(j, i)), 0, T(j, i))
            Next i
        Next j
    End If
    Select_Db = Rcd
    Exit Function
    
errhdlr:
    Rcd(1, 1) = "Erreur n°" & Err.Number & vbCrLf & Err.Description
    Select_Db = Rcd
End Function