-
Solved: connect to oracle using vba
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
-
I would recommend moving this thread to to Access forum.
Last edited by MacroShadow; 06-23-2012 at 10:47 PM.
-
You must install the Oracle Instant Client
Then Follow the instructions outlined here
For information regarding the Instant Client see this
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.
[vba]
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
[/vba]
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.
-
Thanks Macro. I was unaware of that.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules