Drivers to connect to an Oracle Database using VBA

Question

I'm trying to connect to an Oracle Database from an Excel application and whithout a DNS. I found on a website that it's possible to use ADO, so this is why I tried to do. I'm new to this so I juste copied what I found on this website.

Here is my code so far :

Sub ADOtest()

Dim connection As New ADODB.connection

connection.ConnectionString = "UID = user1; PWD= my_pwd; DRIVER = {Microsoft ODBC for Oracle; Server= localhost; Database= orcl.my_domain;"

connection.Open

End sub

When I run this code, I get an error saying that the driver was not found.

The problem is that I have no idea of what I have to do with the driver (how to install it and configure it). Plus, I don't know which one I should use : I've read that there is a driver from Microsoft, another one from Oracle and also I've seen something about providers like msdaora.

The program will be used by many users, so I would like to choose the solution that is the lightest (not much to install on computers).

Thank you !

Answer 1

For COM based ADO (ADODB) you can use the OLE DB Providers.

One is from Oracle, called "Oracle Provider for OLE DB". You can download it from 32-bit Oracle Data Access Components (ODAC) and NuGet Downloads (assuming your Excel is 32-bit). The connection string would be

"Provider=OraOLEDB.Oracle;Data Source=orcl;User ID=myUsername;Password=myPassword"

The other one is from Microsoft. Please note, this provider is deprecated, you should not use it for new projects. Usually it should be available on your Windows. Be aware, like the provider from Oracle it also requires an Oracle Client to be installed on the PC! The connection string would be

"Provider=MSDAORA;Data Source=orcl;User ID=myUsername;Password=myPassword"

The data source is usually defined in tnsnames.ora file or at a LDAP server, for example:

orcl.my_domain =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)          
    )
  )

If you don't have such entry you can put everything into the connection string, e.g.

"Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User ID=myUsername;Password=myPassword"

Perhaps you have to enclose the data source value by double-quotes ("), I am not sure.

So, in any case you would have to install an Oracle Client at all PC's.

Where is your database server hosted? In your question you say Server=localhost;, this would be quite unlikely, i.e. it is in contradiction to The program will be used by many users. I doubt everybody has an Oracle Database server installed on his local host.