• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Oracle connect through VBA

Abhijeet

Active Member
Hi

I want macro to connect Oracle Database is this code is correct or not please tell me

Code:
Sub GetData()

Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Dim recordCount As Long
dbConnectStr = "Provider=msdaora;Data Source=" & "Oracle_Database_Name;"


User Id=userId & "; Password=" & "password"
'The statement above has been commented out. I use the statement below to prompt the user for the userId and password - which is what I prefer!

con.ConnectionString = dbConnectStr
con.Properties("Prompt") = adPromptAlways
con.Open dbConnectStr 'ConnectionString

'This is an example SQL code that you might want to run
'Select * From MyTable

'SQL_String = "Select count(*) from adm_user"
recset.Open SQL_String, con
recset.MoveLast
recordCount = recset.recordCount
recset.MoveFirst
Do While Not recset.EOF = True
'Have a loop here to go through all the fields
recset.MoveNext
Loop
recset.Close
End Sub
 
Which is correct please tell me

Option Explicit

Sub SQLSERVER()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Integer
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Connectionstring = "provider=sqloledb;data source=yoursqlservername;user id=yourusername;password=yourpassword;initial catalog=yourdatabasename"
cn.Open
rs.ActiveConnection = cn
rs.Open "select * from student"
r = 1
Do While Not rs.EOF
Cells(r, 1) = rs.Fields(0)
Cells(r, 2) = rs.Fields(1)
Cells(r, 3) = rs.Fields(2)
Cells(r, 4) = rs.Fields(3)
Cells(r, 5) = rs.Fields(4)
rs.MoveNext
r = r + 1
Loop
Set rs = Nothing
Set cn = Nothing
End Sub
 
Which is correct please tell me

Option Explicit

Sub SQLSERVER()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Integer
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Connectionstring = "provider=sqloledb;data source=yoursqlservername;user id=yourusername;password=yourpassword;initial catalog=yourdatabasename"
cn.Open
rs.ActiveConnection = cn
rs.Open "select * from student"
r = 1
Do While Not rs.EOF
Cells(r, 1) = rs.Fields(0)
Cells(r, 2) = rs.Fields(1)
Cells(r, 3) = rs.Fields(2)
Cells(r, 4) = rs.Fields(3)
Cells(r, 5) = rs.Fields(4)
rs.MoveNext
r = r + 1
Loop
Set rs = Nothing
Set cn = Nothing
End Sub


Hello Abhijeet,

the below code worked well with conecting with Oracle. Here you don't have to loop through but just use the record set. This is much easier, hope you will find it useful:


Code:
Sub OracleLocalConnect()
  Dim RecordSet As New ADODB.RecordSet
  Dim con As New ADODB.Connection
  Dim ExcelRange As Range
  Dim SQLStr As String
  
  Dim ws As Worksheet

  con.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
  "Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=" & _
  "(ADDRESS=(PROTOCOL=TCP)(HOST=LocalHost)(PORT=1521)))" & _
  "(CONNECT_DATA=(SERVICE_NAME=XE)(SERVER=DEDICATED)));" & _
  "User Id=System;Password=oracle;"
  
  con.Open
  Set RecordSet = CreateObject("ADODB.Recordset")

  SQLStr = "SELECT 'Retrieved' Status, per.person_id, per.person_type, per.title, per.first_name, per.last_name " & _
  "FROM  persons  per "
  

  RecordSet.Open SQLStr, con ' , adOpenStatic, adLockReadOnly
  
  Set ws = ActiveWorkbook.Sheets("Persons")
  Set ExcelRange = ws.Range("C3")
  ExcelRange.CopyFromRecordset RecordSet
  
  RecordSet.Close
  con.Close
Exit Sub

End Sub
 
Back
Top