Forum Debian Users Gang

Option Explicit

Public Sub getDataFromDatabase()
    Dim con As Object, rs As Object
    Dim conArgs As String, queryStr As String
    queryStr = "SELECT userName FROM vba WHERE userID = 1"
    conArgs = "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
                "USER=root;" & _
                "PASSWORD=;" & _
                "DATABASE=vba;" & _
                "PORT=3306;" & _
                "OPTION=3;"
                
    'MsgBox conArgs
    'ThisWorkbook.Sheets(1).Cells(1, 1).Value = conArgs
    
    Set con = CreateObject("ADODB.Connection")
    On Error GoTo SQLERRORS
    
    con.Open conArgs
    con.Close
    con = Nothing
    
    
SQLERRORS:
    Dim ce As Object
    For Each ce In con.Errors
      MsgBox ce
    Next ce

Option Explicit

Dim DataBaseCon As Object
Dim RstUsers As Object

Public Sub main()
    Dim QueryString As String
    QueryString = "SELECT * FROM Users"
    Dim ConState As Integer
    ConState = OpenDataBaseConnection()
    
    If ConState Then
        MsgBox "Nie udalo sie nawiazac polaczenia z baza " & ConState
        Exit Sub
    End If
    Set RstUsers = CreateObject("ADODB.Recordset")
    RstUsers.Open QueryString, DataBaseCon
        MsgBox RstUsers.RecordCount
    
    Dim wyniki As Variant
    wyniki = RstUsers.GetRows()
    
    Dim r As Integer, c As Integer
    If RstUsers.RecordCount <> 0 Then
        For r = 0 To UBound(wyniki, 2)
            For c = 0 To UBound(wyniki, 1)
                ThisWorkbook.Sheets(1).Cells(r + 1, c + 1).Value = wyniki(c, r)
            Next c
            
        Next r
    End If
    
        
    
    RstUsers.Close
    Set RstUsers = Nothing
    
    Call CloseDataBaseConnection
       
    
End Sub

Private Function OpenDataBaseConnection() As Integer
    
    Set DataBaseCon = CreateObject("ADODB.Connection")
    Dim ConString As String
    ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=J:/excel2010/Simple.accdb;" & _
                        "Persist Security Info=False;"
    
    On Error GoTo SQLCONHANDLER
    
    DataBaseCon.Open ConString
    DataBaseCon.cursorlocation = 3
    If DataBaseCon.State = 1 Then
        OpenDataBaseConnection = 0
        'Exit Function
    End If
    
    OpenDataBaseConnection = 1
    Exit Function
    
    
SQLCONHANDLER:

    If Not DataBaseCon Is Nothing Then
        If DataBaseCon.State = 1 Then DataBaseCon.Close
    End If
    Set DataBaseCon = Nothing
    Dim ce As Object
    For Each ce In DataBaseCon.Errors
        MsgBox ce
    
    Next ce

End Function


Private Function CloseDataBaseConnection()
    On Error Resume Next
    DataBaseCon.Close
    Set DataBaseCon = Nothing
End Function