VBA - Database and ODBC

VBA - Database and ODBC
*---

	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

view raw sqlExplorer.bas hosted with ❤ by GitHub

---*

VBA - ADODB
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
With conn
.ConnectionString = cnn_str("OracleODBC32") ''OracleODBC32
.CursorLocation = adUseClient
.Open
End With

VBA - ODBC with Connection Strings
*---

	Public Function cnn_str(DB As String)
	Dim cnnStr As String
	Select Case DB
	Case "AccessDB" '''
	''("Provider = Microsoft.ACE.OLEDB.12.0; data source=" & "C:\CodeMiner\CodeMinerDb.accdb")
	cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
	"Data Source=" & "C:\Users\Documents\DqExplorer" & "\" & "ppsdb.accdb"
	Case "SqlExpress"
	cnnStr = "driver={SQL Server};server=mynotebook\SQLEXPRESS;" & _
	"Trusted_Connectin=no;" & _
	"database=myuser;uid=sa;pwd=1111;"
	Case "MSSql"
	cnnStr = ""
	Case "OracleODBC32"
	''BON_DEV_SUNCATA,'BON_DEV_SUNTESTCATA'MYTESTCATA
	cnnStr = ("DRIVER=Oracle in OraClient11g_home1;" & _
	"SERVER=DEV_SERVER;" & _
	"DBQ=DEV_SERVER;" & _
	"UID=myuser;" & _
	"PWD=mypass!01;")
	Case "OracleODBC"
	cnnStr = ("DRIVER=Oracle in OraDb11g_home1;" & _
	"SERVER=DEV_SERVER;" & _
	"DBQ=DEV_SERVER;" & _
	"UID=myuser;" & _
	"PWD=mypass!01;")
	Case "OracleDSN"
	cnnStr = "DSN=dsnOra;UID=myuser;PASSWORD=mypass!01;"
	Case "cnnDQ64" '''
	'''("DRIVER=Oracle in OraDb11g_home1;"--dsn method: dsnCnnDQ
	cnnStr = "DSN=dsnCnnDQ;UID=CATADQM;PASSWORD=CATADQM;"
	Case "cnnDQ32" '''
	cnnStr = ("DRIVER=Oracle in OraClient11g_home1;" & _
	"SERVER=DQ;" & _
	"DBQ=DQ;" & _
	"UID=myuser;" & _
	"PWD=mypass!01;")
	Case "OracleTNS"
	cnnStr = "MYTESTCATA = " & _
	"(DESCRIPTION =" & _
	"(ADDRESS_LIST =" & _
	"(ADDRESS = (PROTOCOL = TCP)(HOST = 10.111.11.111)(PORT = 4013)))" & _
	"(CONNECT_DATA =" & _
	"(SERVICE_NAME = TESTDB)))"
	''........Error.......
	Case "Tibero5ODBC"
	cnnStr = "DRIVER={Tibero 5 ODBC Driver};SERVER=10.10.10.10;PORT=1521;DB=myDB;UID=myuser;PWD=mypass"
	Case "Tibero5DSN"
	cnnStr = "DSN=TiberoNew;UID=myuser;PASSWORD=mypass;CHARSET=MSWIN949;"
	Case "Tibero5OLEDB"
	cnnStr = "Provider=tbprov.Tbprov.5;Data Source=10.10.10.10,1521,RWISDB;User ID=myuser;Password=mypass;" '성공'Initial Catalog="";"
	Case Else
	cnnStr = ""
	End Select
	Debug.Print cnnStr & "...Connected.!"
	cnn_str = cnnStr
	End Function

view raw cnn_str.bas hosted with ❤ by GitHub

---*

VBA - Excel SQL Solution

---

---