DSN-Less connections with MS Oracle Driver

[Leif]

I'm trying to make Doug Steel's DSN-Less code work for an Oracle connection
via an MS driver. My connection string looks like:

tdfCurrent.Connect = "ODBC;" & _
"Driver={Microsoft ODBC for Oracle};" & _
"Server=DSLARCM1;" & _
"Database=merp;" & _
"Uid=myUid;" & _
"Pwd=myPwd"


I'm trying to convert a linked table (linked via ODBC MS Driver) to
DSN-Less. The error I get is at the statement:

dbCurrent.TableDefs.Append tdfCurrent

The error is

"ODBC--connection to '{Microsoft ODBC for Oracle}DSLARCM1' failed. (3151
encountered"

However, if I use the Oracle driver for Oracle it works. The connection
string is:

tdfCurrent.Connect = "ODBC;" & _
"Driver={Oracle ODBC Driver};" & _
"Dbq=COMET_DSLARCM1_MERP.WORLD;" & _
"Uid=myUid;" & _
"Pwd=myPwd"

The TNSnames.ora entry looks like:
COMET_DSLARCM1_MERP.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host =
dslarcm1)(Port = 1521))
(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host =
dslarcm1)(Port = 1526))
)
(CONNECT_DATA =
(SID = merp)
)
)

However, few people have the Oracle driver, most have the MS driver, so I
would prefer to set it up to use the MS driver.

Regards,
Leif Eriksen

 

[Stefan Hoffmann]

hi Leif,

However, few people have the Oracle driver, most have the MS driver, so I
would prefer to set it up to use the MS driver.

I never got the MS driver working, is suspect i don't set it up correctly.

As the Oracle drivers are free and stable, why not using it?

Just my 2 cents...

mfG
--> stefan <--

 

[Douglas J. Steele]

I don't see the ability to specify the database in what Carl Prothman has at
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForOracleFromMicrosoft

Also, should it perhaps be "Server=DSLARCM1.world;" ?

(Sorry, I don't work with Oracle)

 

[Leif]

Doug,

Thanks for your reply. You are correct. Once I took out the database line
I was able to connect. I guess I just left it in from a previous use for SQL
Server.

Regards,
Leif

 

[Leif]

Thanks Stefan for your reply.

I discovered the problem, which was the database line in my string (it
needed to be removed). However, I also found that the MS driver, on its own,
is not enough. I still need an Oracle Client install for the MS Oracle
driver to work. However, once that is done it does seem the MS driver is
faster than the Oracle driver, which is surprising.

Regards,
Leif