Dim CN As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Ako, Ikaw As String


Private Sub Command1_Click()
Call TheConn
With ListView1

Dim sDatabase As String
Dim sSQL As String
sDatabase = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=sometest; User=root;Password=; "
sSQL = "Select * From users"

Set CN = New ADODB.Connection

CN.Open sDatabase
Set rs = New ADODB.Recordset
rs.Open sSQL, CN
DTPicker1 = Format(DTPicker1.Value, DTPicker1.CustomFormat)
DTPicker2 = Format(DTPicker2.Value, DTPicker2.CustomFormat)
ListView1.ListItems.Clear


Set rs = New ADODB.Recordset
    Ikaw = "SELECT * FROM users WHERE date_today between '" & Format(DTPicker1.Value, "yyyy/MM/dd ") & "' AND '" & Format(DTPicker2.Value, "yyyy/MM/dd ") & "'"
    rs.Open Ikaw, CN, adOpenDynamic, adLockReadOnly
        Do While Not rs.EOF
            Set aaa = .ListItems.Add(, , rs!firstname)
                aaa.SubItems(1) = rs!lastname
                aaa.SubItems(2) = rs!age
            rs.MoveNext
        Loop

DTPicker1 = Format(Now, DTPicker1.CustomFormat)
DTPicker2 = Format(Now, DTPicker2.CustomFormat)
End With


End Sub

Private Sub Command2_Click()
  Set conn = New ADODB.Connection
  conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=sometest; user=root; password= "
  conn.Open

   aaa = "insert into users(firstname,lastname,age) values('" & Text1 & "','" & Text2 & "','" & Text3 & "')"
   conn.Execute aaa





End Sub

Private Sub TheConn()
    Set CN = New ADODB.Connection
    Ako = "Provider=MICROSOFT.JET.OLEDB.4.0; DATA SOURCE=" & App.Path & "\Lv.mdb"
    CN.ConnectionString = Ako
    CN.Open
End Sub

Private Sub Command3_Click()

 Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object


    Dim recArray As Variant

    Dim strDB As String
    Dim fldCount As Integer
    Dim recCount As Long
    Dim iCol As Integer
    Dim iRow As Integer
    Dim aa As String
    Dim aal As ListItem




 Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")


sDatabase = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=sometest; User=root;Password=; "

        If CheckBox1.Checked Then
            Menu_List.Add ("firstname")
        Else
            Menu_List.Remove ("firstname")
        End If

        If CheckBox2.Checked Then
            Menu_List.Add ("lastname")
        Else
            Menu_List.Remove ("lastname")
        End If

        If CheckBox3.Checked Then
            Menu_List.Add ("age")
        Else
            Menu_List.Remove ("age")
        End If

        If CheckBox4.Checked Then
            Menu_List.Add ("date_today")
        Else
            Menu_List.Remove ("date_today")
        End If
        aal = [String].Join(", ", aal.ToArray())

sSQL = "SELECT " + MenuList + " FROM users WHERE date_today between '" & Format(DTPicker1.Value, "yyyy/MM/dd ") & "' AND '" & Format(DTPicker2.Value, "yyyy/MM/dd ") & "'"
Set cnt = New ADODB.Connection

cnt.Open sDatabase
Set rst = New ADODB.Recordset
rst.Open sSQL, cnt


    ' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True

    ' Copy field names to the first row of the worksheet
    fldCount = rst.Fields.Count

    For iCols = 0 To fldCount - 1
        xlWs.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
    Next


    ' Check version of Excel
    If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
        'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset

        ' Copy the recordset to the worksheet, starting in cell A2
        xlWs.Cells(2, 1).CopyFromRecordset rst
        'Note: CopyFromRecordset will fail if the recordset
        'contains an OLE object field or array data such
        'as hierarchical recordsets

    Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel

        ' Copy recordset to an array
        recArray = rst.GetRows
        'Note: GetRows returns a 0-based array where the first
        'dimension contains fields and the second dimension
        'contains records. We will transpose this array so that
        'the first dimension contains records, allowing the
        'data to appears properly when copied to Excel

        ' Determine number of records

        recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array


        ' Check the array for contents that are not valid when
        ' copying the array to an Excel worksheet
        For iCol = 0 To fldCount - 1
            For iRow = 0 To recCount - 1
                ' Take care of Date fields
                If IsDate(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                ' Take care of OLE object fields or array fields
                ElseIf IsArray(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = "Array Field"
                End If
            Next iRow 'next record
        Next iCol 'next field

        ' Transpose and Copy the array to the worksheet,
        ' starting in cell A2
        xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
            TransposeDim(recArray)
    End If

    ' Auto-fit the column widths and row heights
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit

    ' Close ADO objects
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

    ' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing

    Set xlApp = Nothing



End Sub



Private Sub Command4_Click()
ListView1.ListItems.Clear

End Sub

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

    Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

    Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)

    ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X

    TransposeDim = tempArray


End Function


Private Sub Form_Load()
Dim sDatabase As String
Dim sSQL As String
sDatabase = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=sometest; User=root;Password=; "
sSQL = "Select * From users"

Set CN = New ADODB.Connection

CN.Open sDatabase
Set rs = New ADODB.Recordset

rs.Open sSQL, CN

Do Until (rs.EOF)
    List1.AddItem rs.Fields("firstname")
     rs.MoveNext
Loop

DTPicker1 = Format(Now, DTPicker1.CustomFormat)
DTPicker2 = Format(Now, DTPicker2.CustomFormat)


 Image1.Height = Me.Height
 Image1.Width = Me.Width



End Sub