I have a problem at hand which requires me to write a VBA code in Excel to connect to an Oracle database, execute a few queries and then store the result in an Excel spreadsheet. I connect to the database through TOAD remotely through Citrix, i.e. I do not have TOAD installed locally in my machine and access it through Citrix and then access the database. I have tried connecting to the database by creating an ADODB object and tried out various combinations of connection strings given in the below website:
However, none of the examples given in this site has helped. Given below is a code snippet of one of the ways I tried to connect:
Private Sub CommandButton1_Click()
Dim ConnStr As String Dim adodbCnn As Object Dim resultSet As Object
ConnStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=172.18.64.155)(Port=1521))(CONNECT_DATA=(SID=SPCH)));User Id=TBALDE;Password=TBALDE_123" Set adodbCnn = CreateObject("ADODB.Connection") On Error GoTo EH adodbCnn.Open ConnStr
Exit Sub
EH:
MsgBox ("Error# " & adodbCnn.Errors(0).NativeError & ": " & adodbCnn.Errors(0).Description) Stop
End Sub
All help in this regard would be greatly apprectiated.
Thanks, Karthik.
