Hi Team,
I am using ADO-DB.Connection in excel to connect to my database which is recently upgraded to 11g from 9i.
I am facing a strange problem after upgrading to oracle database to 11g.
One query which was working fine with oracle 9i when accessing through excel is now hanging and not responding. Here is my code , its failing with both ODBC driver and also provider MSDAORA.
But when the same query which we execute in the Toad/SQL developer its giving records, we are not getting output without any error/hanging
Can you please let us know if ADODB is comaptible with oracle 11g or its nothing to do with oracle version?
Also can you please let us know if we can use any other way to connect to Oracle database using the excel vba instead of ADODB. I mean directly using DSN without ADODB and connect to excel ,if yes please let us know the syntax also.
Please help us by giving any solution.
Thanks in Advance!!
Dim cn As ADODB.Connection
Dim CMD As ADODB.Command
Dim strconn As String
Set cn = New ADODB.Connection
Set CMD = New ADODB.Command
cn.Open ("Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=hkdbmirror01.thg.local)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=PDB))); uid=hkreader; pwd=hkreader;")
or
cn.Open ( _
"User ID=hkreader" & _
";Password=hkreader" & _
";Data Source=Intrepid" & _
";Provider=MSDAORA")
CMD.CommandText = "select tag_id, answer from h_metric_answer_kk where (version_id = 1099) and (sub_project_id = 1023181) and (version_tax_node_metric_id = 392464) and ((tag_id is not null) and (tag_id in (248679,248677,248678)))"
CMD.ActiveConnection = cn
CMD.Execute
MsgBox ("Success")
cn.Close
I am using ADO-DB.Connection in excel to connect to my database which is recently upgraded to 11g from 9i.
I am facing a strange problem after upgrading to oracle database to 11g.
One query which was working fine with oracle 9i when accessing through excel is now hanging and not responding. Here is my code , its failing with both ODBC driver and also provider MSDAORA.
But when the same query which we execute in the Toad/SQL developer its giving records, we are not getting output without any error/hanging
Can you please let us know if ADODB is comaptible with oracle 11g or its nothing to do with oracle version?
Also can you please let us know if we can use any other way to connect to Oracle database using the excel vba instead of ADODB. I mean directly using DSN without ADODB and connect to excel ,if yes please let us know the syntax also.
Please help us by giving any solution.
Thanks in Advance!!
Dim cn As ADODB.Connection
Dim CMD As ADODB.Command
Dim strconn As String
Set cn = New ADODB.Connection
Set CMD = New ADODB.Command
cn.Open ("Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=hkdbmirror01.thg.local)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=PDB))); uid=hkreader; pwd=hkreader;")
or
cn.Open ( _
"User ID=hkreader" & _
";Password=hkreader" & _
";Data Source=Intrepid" & _
";Provider=MSDAORA")
CMD.CommandText = "select tag_id, answer from h_metric_answer_kk where (version_id = 1099) and (sub_project_id = 1023181) and (version_tax_node_metric_id = 392464) and ((tag_id is not null) and (tag_id in (248679,248677,248678)))"
CMD.ActiveConnection = cn
CMD.Execute
MsgBox ("Success")
cn.Close