Database and ODBC

sqlExplorer.bas

Public Sub sqlExplorer()
    Dim wb As New Workbook
    Dim ws As New Worksheet
    Set wb = ActiveWorkbook ''ThisWorkbook
    Set ws = wb.Sheets("SqlExplorer")
    Dim qry_text As Variant
    Dim rngHeader As range
    Dim rngResult As range
    
    qry_text = range("qry_text").Value    
    If IsEmpty(qry_text) Or (InStr(1, LCase(qry_text), "select") = 0 And InStr(1, LCase(qry_text), "from")) Then
        Exit Sub
    End If
    ''
    ''Headers clear...
    Set rngHeader = ws.range("qry_header")
    rngHeader.EntireRow.ClearContents
    ''Data clear
    Set rngResult = ws.range("qry_result")
    ws.range(rngResult.row & ":" & rngResult.offset(1000, 0).row).ClearContents
    
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    With conn
      '''.ConnectionString = cnn_str("AccessDB")   ''AccessODBC
        .ConnectionString = cnn_str("OracleODBC32")   ''OracleODBC32
        ''.ConnectionString = cnn_str("OracleODBC")   ''OracleODBC
      '''.ConnectionString = cnn_str("OracleDSN")   ''OracleDSN
      .CursorLocation = adUseClient
      .Open
    End With

    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''Make Column header
    ''
    Dim ix As Integer
    Dim row_count As Integer
    rs.PageSize = 1000
    rs.MaxRecords = 1000
    rs.Open qry_text, conn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly
    '''row_count = rs.RecordCount
    For ix = 0 To rs.Fields.Count - 1
        ''ws.Cells(Range("results_table").Row, ix + 1) = rs.Fields(ix).Name
        ws.Cells(rngHeader.row, ix + 1) = rs.Fields(ix).Name
    Next ix

    '''''''''''''''''''''''''''''''''''''''''''''''''
    Dim rows_limit As Variant
    rows_limit = 1000
    rngResult.Cells(1, 1).CopyFromRecordset rs, rows_limit
''    Dim rrn As Integer
''    rrn = 1
''    Do Until rs.EOF
''        If rrn = 1 Then
''        End If
''            ws.Cells(rrn, 1) = rsH("rate_match") & "Case " & rsH("agid") & rsH("bgid")
''        rrn = rrn + 1
''        rs.MoveNext
''    Loop
    rs.Close
    '''''''''''''''''
    '''Application.StatusBar = row_count & " rows retrieved...Query completed.(Limited: " & rows_limit & ")"
    Application.StatusBar = " rows retrieved...Query completed.(Limited: " & rows_limit & ")"
    '''''''''''''''''''''
    'Destroy objects
    conn.Close
    Set conn = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub