DSN-less VBA connection to Oracle

Eileen Wallace · Forum Member Joined: 10 Sep 2002 Posts: 3

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?

ao730 · Posted: Fri Mar 21, 2003 11:36 am Post subject:

I use ADO and the following code:

Eileen Wallace · Forum Member Joined: 10 Sep 2002 Posts: 3

Thanks Tim - I'll try it out.

abductee · Senior Member Joined: 05 Sep 2006 Posts: 82

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

rajx72

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

abductee · Senior Member Joined: 05 Sep 2006 Posts: 82

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

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

rajx72

hello

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

abductee · Senior Member Joined: 05 Sep 2006 Posts: 82

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

rajx72

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

abductee · Senior Member Joined: 05 Sep 2006 Posts: 82

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?

uthee_85 · Forum Member Joined: 03 Jul 2008 Posts: 1

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

vparikh1

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

jcastillo · Forum Member Joined: 20 Oct 2008 Posts: 1

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.