Solved: connect to oracle using vba

[sauda]

Dear Team,
Hi.......
Today I came to know about this forum. I am new to this lang.
I want to connect to oracle database using VBA. can anyone help me the simplest way to write macro. I googled but not getting proper solution.
Connection to db--> Username=scott password=tiger server=oracle.

I want to retrieve data from database using sql code below

select e.ename,e.deptno,d.dname, b.percentage
from emp e, dept d, bonus b
where e.deptno=d.deptno
and e.sal=b.sal

[MacroShadow]

I would recommend moving this thread to to Access forum.

[MacroShadow]

You must install the Oracle Instant Client (http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html)
Then Follow the instructions outlined here (http://www.techonthenet.com/access/questions/oracle_connect.php)
For information regarding the Instant Client see this (http://www.databasejournal.com/features/oracle/article.php/3371561)

Basically you set up an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.
Then you will use this function to actually connect to the db, and from there you use your SQL normally.


Function OracleConnect() As Boolean

Dim ws As Workspace
Dim db As Database
Dim LConnect As String

On Error GoTo Err_Execute

'Use {Microsoft ODBC for Oracle} ODBC connection
LConnect = "ODBC;DSN=AAAA;UID=scott ;PWD=tiger ;SERVER=oracle"

'Point to the current workspace
Set ws = DBEngine.Workspaces(0)

'Connect to Oracle
Set db = ws.OpenDatabase("", False, True, LConnect)
db.Close
OracleConnect = True
Exit Function
Err_Execute:
MsgBox "Connecting to Oracle failed."
OracleConnect = False

End Function


Note that you will have to change AAAA in the above code to the name of the ODBC Data Source that you set up.

You may also want to read this (http://www.johnsmiley.com/cis18/Smiley004.pdf).

[sauda]

Thanks Macro. I was unaware of that.