Connect Excel to Oracle

Question

Hi

Can we connect from excel (excel-VBA) to oracle(in a remote server) without installing oracle client in the client system... I tried all the options below but it is throwing error as oracle client should be installed.

below is the connectionstring i have used

1)strConnection = "Provider=OraOLEDB.Oracle;Data Source=SourceName;User Id=Username;
                   Password=password;"  
2)strConnection = "Provider=MSDAORA;Data Source=SourceName;UserId=Username;
                    Password=password;"
3)strConnection = "Provider=MSDAORA.1;User ID=Username/password;Data
                   Source=SourceName;Persist Security Info=False"
4)strConnection = "Driver={Microsoft ODBC for Oracle};SERVER=(DESCRIPTION=
                  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.6)(PORT=1521)
                 )(CONNECT_DATA (SERVICE_NAME=SERVICEName)));uid=Username;
                   pwd=password;Uid=Username;Pwd=password;"  
5)strConnection = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.6)
                   (PORT=1521))(CONNECT_DATA =(SERVICE_NAME=SERVICEName)));
                   uid=Username;pwd=password;"

Answer 1

You need some form of client to connect to an Oracle database, local or remote.

You have two choices:

1. Install an Oracle SQL*Net client. If your server is Oracle 10g or higher you don't need the full-blown client: you can use the more lightweight Instant Client.
2. Buy an n-tier ODBC driver which supports generic connectivity. OpenLink is one, there are others.

---

".can we not connect to the oracle database with only the Drivers or providers present in our OS(Windows XP).."

Think of it this way: the ODBC drivers do not connect to the database, they connect to SQL*Net. Then SQL*Net connects to the database.

"what is the difference between Drivers,providers with the client. "

The Oracle provided drivers are better tuned for Oracle than the ones MS provides, but in this context they are the same: both require the presence of an Oracle client to work.