Oracle database connection in vba

%3CLINGO-SUB%20id%3D%22lingo-sub-130016%22%20slang%3D%22en-US%22%3EOracle%20database%20connection%20in%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-130016%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%2C%3C%2FP%3E%0A%3CP%3Ei%20am%20trying%20to%20connect%20oracle%20database%20but%20here%20i%20am%20facing%20issue%20to%20choose%20Driver(provider)%20which%20i%20have%20to%20use%20here%20.i%20have%20selected%20%22microsoft%20activex%20data%20object%202.5%20library%22%20.My%20machine%20is%2064%20bit%20widow%2010%20with%20office%202016%20.%20can%20anyone%20please%20suggest%20which%20driver%20i%20need%20to%20install%20for%20oracle%20connection%20.it%20will%20great%20please%20provide%20me%20the%20link%20where%20to%20download.i%20tried%20to%20install%20%22ODAC121024_x64%22%20but%20due%20to%20some%20reason%20it%20hang%20for%20long%20time%20in%20pree-checks%20.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESub%20dev_connection()%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20strConnection%20As%20String%3CBR%20%2F%3EDim%20conn%20As%20ADODB.Connection%3CBR%20%2F%3EDim%20rs%20As%20ADODB.Recordset%3CBR%20%2F%3EDim%20strSQL%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20conn%20%3D%20New%20ADODB.Connection%3CBR%20%2F%3ESet%20rs%20%3D%20New%20ADODB.Recordset%3CBR%20%2F%3E%3CBR%20%2F%3EstrConnection%20%3D%20%22%3CSTRONG%3EProvider%3DMicrosoft%20OLE%20DB%20Provider%20for%20Oracle%3B%3C%2FSTRONG%3EData%20Source%3D0.0.0.0%3A1521%3Axe%3BUser%20Id%3Dsystem%3BPassword%3Doracle%3B%22%3CBR%20%2F%3E%3CBR%20%2F%3Econn.ConnectionString%20%3D%20strConnection%3CBR%20%2F%3Econn.Open%3CBR%20%2F%3E%3CBR%20%2F%3EstrSQL%20%3D%20%22SELECT%20*%20FROM%20dual%22%3CBR%20%2F%3Ers.Open%20strSQL%2C%20conn%2C%203%2C%203%3CBR%20%2F%3E%3CBR%20%2F%3Ers.MoveFirst%3CBR%20%2F%3EWhile%20Not%20rs.EOF%3CBR%20%2F%3EMsgBox%20rs(1)%3CBR%20%2F%3Ers.MoveNext%3CBR%20%2F%3EWend%3CBR%20%2F%3Ers.Close%3CBR%20%2F%3Econn.Close%3CBR%20%2F%3ESet%20conn%20%3D%20Nothing%3CBR%20%2F%3ESet%20rs%20%3D%20Nothing%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%0A%3CP%3E%3COBJECT%20data-extension-version%3D%220.5.0.161%22%20data-supports-flavor-configuration%3D%22true%22%20data-install-updates-user-configuration%3D%22true%22%20id%3D%22__symantecPKIClientMessenger%22%20style%3D%22display%3A%20none%3B%22%3E%3C%2FOBJECT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-130016%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3224787%22%20slang%3D%22en-US%22%3ERe%3A%20Oracle%20database%20connection%20in%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3224787%22%20slang%3D%22en-US%22%3EPlease%20find%20useful%20links%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F60441911%2Foracle-client-and-network-components-not-found%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F60441911%2Foracle-client-and-network-components-not-found%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello ,

i am trying to connect oracle database but here i am facing issue to choose Driver(provider) which i have to use here .i have selected "microsoft activex data object 2.5 library" .My machine is 64 bit widow 10 with office 2016 . can anyone please suggest which driver i need to install for oracle connection .it will great please provide me the link where to download.i tried to install "ODAC121024_x64" but due to some reason it hang for long time in pree-checks .

 

Thank you in advance.

 

Sub dev_connection()

Dim strConnection As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

strConnection = "Provider=Microsoft OLE DB Provider for Oracle;Data Source=0.0.0.0:1521:xe;User Id=system;Password=oracle;"

conn.ConnectionString = strConnection
conn.Open

strSQL = "SELECT * FROM dual"
rs.Open strSQL, conn, 3, 3

rs.MoveFirst
While Not rs.EOF
MsgBox rs(1)
rs.MoveNext
Wend
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub

1 Reply