BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

DSN-less VBA connection to Oracle


 
Search this topic... | Search SDK (VBA/ASP/JSP)... | Search Box
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Author Message
Eileen Wallace
Forum Member
Forum Member



Joined: 10 Sep 2002

Posts: 3



PostPosted: Fri Mar 21, 2003 9:32 am 
Post subject: DSN-less VBA connection to Oracle

Can anyone tell me how I can create a DSN-less connection to an Oracle database using VBA? I do not want to have to pre-define an ODBC DSN. Also, what is the best way to attempt this? ADO? DAO? RDO? MDAC? Why are there so many different ways to connect to a database? crazy.gif
Back to top
ao730
Forum Member
Forum Member



Joined: 27 Feb 2003

Posts: 16
Location: Michigan



PostPosted: Fri Mar 21, 2003 11:36 am 
Post subject:

I use ADO and the following code:

Code:

    'Declare variables
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim sSQL As String
   
    'Create objects
    Set CN = New ADODB.Connection
    Set RS = New ADODB.Recordset

    CN.CursorLocation = adUseClient
    CN.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=<PASSWORD>;Persist Security Info=True;User ID=<USER ID>;Data Source=<DATABASE NAME>"
    CN.Open    'Open connection
   
    'Define SQL String
    sSQL = "SELECT * FROM MMS_PO"

    'Open Recordset
    RS.Open sSQL, CN, adOpenStatic, adLockOptimistic


Quote:
Why are there so many different ways to connect to a database?

Like everything else, database connections methods have evolved. ADO.NET is the "newest" method, and is suppose to be addressing the short comings of ADO, which in turn was addressing the short comings of RDO which was addressing the short comings of DAO. I prefer ADO, only because I'm familiar with it, and haven't quite gotten the hang of ADO.NET or had a real need for ADO.NET yet.

By the way, I still use a test.udl file on my machine to develop the connection string and feel it's a pretty useful and cheap tool.

Hope this helps
Tim

_________________
Tim Walsh
Goal: Doing it when the experts say it can't be done
Back to top
Eileen Wallace
Forum Member
Forum Member



Joined: 10 Sep 2002

Posts: 3



PostPosted: Fri Mar 21, 2003 1:18 pm 
Post subject: DSN-less VBA connection to Oracle

Thanks Tim - I'll try it out.
Back to top
abductee
Senior Member
Senior Member



Joined: 05 Sep 2006

Posts: 92


flag
PostPosted: Mon Sep 18, 2006 6:14 am 
Post subject: Re: DSN-less VBA connection to Oracle

whats a test.udl file ?

i've tried about 4 different bits of VBA to get a connection to work, the original VBA (created by someone who left before i joined) no longer works - think i'm missing the right reference in my VBA on my machine

this is the code thats stopped working for me

dbconnection.Open "Provider=OraOLEDB.Oracle;User ID=highways;Password=highways;Data Source=swimsp"
dbconnection.CursorLocation = adUseClient

and this is the error; "run-time error '424' object required"

I have tried the code above ao730's post

and on line "Set CN = New ADODB.Connection"
i get a "run-time error '429' Active X cant create object" - error message


i can ping the database and setup a system DSN file that when tested connects to the swimsp database

_________________
o
|__/\o
(Oo}
(=--) .'===o- ~Z~A~P~
/'''''\/ /U'
l l \_/
\\__)
E ' /
| ||
| || The
(__\\ Abductee

SYSTEM:
BOXI 3.1 - SP 6
4 x Microsoft Window server 2003 Enterprise Edition SP 2 build 3790 32-BIT
CPU 2 x 2533MHZ / RAM 16GB / TOMCAT 7 / JRE 1.6.0.17
Back to top
rajx72
Forum Associate
Forum Associate



Joined: 02 Aug 2006

Posts: 754
Location: CA



PostPosted: Mon Sep 18, 2006 10:42 am 
Post subject: Re: DSN-less VBA connection to Oracle

Hi

Check the reference to ADO object by going to Tools and then Reference. then check the option Microsoft Active X data object 2.5 or 2.6 library from the list.

This kind of error comes when the connection object is not initialized properly.

hope this helps
Back to top
abductee
Senior Member
Senior Member



Joined: 05 Sep 2006

Posts: 92


flag
PostPosted: Tue Sep 19, 2006 5:14 am 
Post subject: Re: DSN-less VBA connection to Oracle

I have Microsoft Active X data object 2.5 selected

it wont let me select 2.6 at the same time

if i drop 2.5 and add 2.6 i get the same error

nonod.gif

the only references i have selected are;
visual basic for applications
businessobjects 6.5 object library
ole automation
microsoft 2.0 object library
microsoft activex data objects 2.6 library

banghead.gif

_________________
o
|__/\o
(Oo}
(=--) .'===o- ~Z~A~P~
/'''''\/ /U'
l l \_/
\\__)
E ' /
| ||
| || The
(__\\ Abductee

SYSTEM:
BOXI 3.1 - SP 6
4 x Microsoft Window server 2003 Enterprise Edition SP 2 build 3790 32-BIT
CPU 2 x 2533MHZ / RAM 16GB / TOMCAT 7 / JRE 1.6.0.17
Back to top
rajx72
Forum Associate
Forum Associate



Joined: 02 Aug 2006

Posts: 754
Location: CA



PostPosted: Wed Sep 20, 2006 9:39 pm 
Post subject: Re: DSN-less VBA connection to Oracle

hello

Did u check your connection variable name. as per your code which is
Code:

dbconnection.Open "Provider=OraOLEDB.Oracle;User ID=highways;Password=highways;Data Source=swimsp"
dbconnection.CursorLocation = adUseClient

and this is the error; "run-time error '424' object required"

I have tried the code above ao730's post

and on line "Set CN = New ADODB.Connection"


you are setting CN as new connection and using dbconnection.open

check your variable declaration.
Back to top
abductee
Senior Member
Senior Member



Joined: 05 Sep 2006

Posts: 92


flag
PostPosted: Wed Oct 04, 2006 6:46 am 
Post subject: Re: DSN-less VBA connection to Oracle

the

dbconnection.Open "Provider=OraOLEDB.Oracle;User ID=highways;Password=highways;Data Source=swimsp"
dbconnection.CursorLocation = adUseClient

code was different to ao730's code which was "Set CN = New ADODB.Connection"

i tried all of ao730's code and got the cant create object error - which makes me think i'm missing somthing on my machine

_________________
o
|__/\o
(Oo}
(=--) .'===o- ~Z~A~P~
/'''''\/ /U'
l l \_/
\\__)
E ' /
| ||
| || The
(__\\ Abductee

SYSTEM:
BOXI 3.1 - SP 6
4 x Microsoft Window server 2003 Enterprise Edition SP 2 build 3790 32-BIT
CPU 2 x 2533MHZ / RAM 16GB / TOMCAT 7 / JRE 1.6.0.17
Back to top
rajx72
Forum Associate
Forum Associate



Joined: 02 Aug 2006

Posts: 754
Location: CA



PostPosted: Wed Oct 04, 2006 11:53 am 
Post subject: Re: DSN-less VBA connection to Oracle

hi

instead of using dsnless try using dsn connection. Make a dsn and use it. see if it gives the same error or not.


thanks
Back to top
abductee
Senior Member
Senior Member



Joined: 05 Sep 2006

Posts: 92


flag
PostPosted: Mon Oct 09, 2006 6:32 am 
Post subject: Re: DSN-less VBA connection to Oracle

ok i have set up a connection to the database as a system data source (System DSN) and have tested the connection and it works

now what?

_________________
o
|__/\o
(Oo}
(=--) .'===o- ~Z~A~P~
/'''''\/ /U'
l l \_/
\\__)
E ' /
| ||
| || The
(__\\ Abductee

SYSTEM:
BOXI 3.1 - SP 6
4 x Microsoft Window server 2003 Enterprise Edition SP 2 build 3790 32-BIT
CPU 2 x 2533MHZ / RAM 16GB / TOMCAT 7 / JRE 1.6.0.17
Back to top
uthee_85
Forum Member
Forum Member



Joined: 03 Jul 2008

Posts: 1



PostPosted: Thu Jul 03, 2008 12:16 am 
Post subject: Re: DSN-less VBA connection to Oracle

guys can you all help me i have trouble with error object required.
this the code:
Code:

Dim strConnection, conn, rs
Dim strSQL As String
Set strConnection = New Connection
Set conn = New ADODB.Connection
Set rs = New recordset


strConnection = "Provider=OraOLEDB.oracle;Data Source=192.168.1.234:1521:xe;User Id=viola-0.5.0;Password=123456;"


Set conn.strConnection = Server.CreateObject("ADODB.Connection")
conn.open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM AIRLINES"
rs.open strSQL, conn, 3, 3

rs.MoveFirst
While Not rs.EOF
     RSWApp.WriteToLog rs("ID"), rs("CODE"), rs("NAME")
rs.MoveNext
Wend


banghead.gif
Back to top
vparikh1
Senior Member
Senior Member



Joined: 20 Feb 2003

Posts: 50
Location: Mumbai


flag
PostPosted: Thu Jul 10, 2008 3:20 am 
Post subject: Re: DSN-less VBA connection to Oracle

Hi,
On which line is the error coming?
My guess is that there is a reference to RSWApp in the code mentioned below. You may need to initiate the object or provide the reference to the object under Tools-->Reference

Thanks
Vivek
Back to top
jcastillo
Forum Member
Forum Member



Joined: 20 Oct 2008

Posts: 1



PostPosted: Mon Oct 20, 2008 4:27 pm 
Post subject: Re: DSN-less VBA connection to Oracle

Hello,

I am trying to create a connection to Oracle from VBA, but when the debugger reaches the following instructions...

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider = MSDAORA;Data Source = RTLP;User ID = apps;Password = apps"
cn.Open

I get the following error message:

Run-time error '-2147467259 (80004005)': Oracle error occurred, but error message could not be retrieved from Oracle.

Any help on this issue, will be greatly appreciated.

Thanks in advance,

Jesus C.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0499 seconds using 17 queries. (SQL 0.0037 Parse 0.0356 Other 0.0106)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo