VBA- Excel to Oracle DB Connection Issue

[renithr]

Hi All,

The below code to connect excel to oracle database is showing run time error.
Error at : src_conn.Open varConn

Did anyone phased this issue before?

Please let me know if anyone can resolve this issue....

Private src_conn As ADODB.Connection
Private Rec_set As ADODB.Recordset
Sub Ora_Connection()

Dim OraSession As Object
Dim OraDatabase As Object
Dim EmpDynaset As Object
Dim flds() As Object
Dim fldcount As Integer

Set src_conn = New ADODB.Connection

varConn = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=server)(PORT=1521))" & _
         "(CONNECT_DATA=(SERVICE_NAME=sname))); uid=uname;pwd=passwd;"

src_conn.CommandTimeout = 9999999
src_conn.Open varConn

Set Rec_set = New ADODB.Recordset
                
Sql = "select count(*) from table"
                 
Rec_set.Open Sql, src_conn
Dim mows As Worksheet

mows = ThisWorkbook.Worksheets("Sheet1")
With mows.QueryTables.Add(Connection:=Rec_set, Destination:=mows.Range(A1, B1))
                        .Refresh BackgroundQuery:=False
End With

End Sub

 

[jimrward]

i dont see a parameter for database name in your varConn setup, is this required