In the past i've managed to connect to an MS access database by declaring the data objects and using the folling line:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Fault1.mdb;"
Then i can perform all my queries and close the connection. Job done. Now I want to do the same connecting to an Oracle DB on a network. All the Oracle ODBC settings have been created in the DSN.I recorded my actions in a macro when connecting to my Oracle DB to retrive a query, and I get the following:
With ActiveSheet.QueryTables.Add(Connection:= _
<sql query .. etc>
I want to connect to Oracle using method 1, so I can perform many queries from excel and then close the connection??
Help much appreciated.