The following will work.
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyOraDb;" & _
"User ID=scott;Password=tiger;"
con.Open
I have used source as MyOraDb, user id as scott as password as tiger. Change these values as per your requirement.