Access pass-through query using dsn-less connection not working

V

Vince

On my Win2k PC I'm trying to execute a pass through query in Access 97
(Access 2000 gives me the same results) per a dsn-less connection against an
Oracle 9.2.0.4 database residing on a unix server.

I keep getting an eror window: "ODBC -- call failed"

In that window I click OK and get another window: "[Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified(#0)"


Here's the value in Query Properties|ODBC Connect Str:

ODBC;connect_string = "Driver={Oracle in Ora920}; Host=hostname; Port=1521;
SID=thesid; UID=myid; PWD=mypwd;"

If I remove "UID=myid; PWD=mypwd;" from connect_string, I'm prompted to
select a dsn. If I make a selection, I'm prompted for userid/password,
supply same, and all works.

I've tried numerous drivers, none work. Is something wrong with the syntax
of my connect_string? Other suggestions?
 
D

Dirk Goldgar

Vince said:
On my Win2k PC I'm trying to execute a pass through query in Access 97
(Access 2000 gives me the same results) per a dsn-less connection
against an Oracle 9.2.0.4 database residing on a unix server.

I keep getting an eror window: "ODBC -- call failed"

In that window I click OK and get another window: "[Microsoft][ODBC
Driver Manager] Data source name not found and no default driver
specified(#0)"


Here's the value in Query Properties|ODBC Connect Str:

ODBC;connect_string = "Driver={Oracle in Ora920}; Host=hostname;
Port=1521; SID=thesid; UID=myid; PWD=mypwd;"

If I remove "UID=myid; PWD=mypwd;" from connect_string, I'm prompted
to select a dsn. If I make a selection, I'm prompted for
userid/password, supply same, and all works.

I've tried numerous drivers, none work. Is something wrong with the
syntax of my connect_string? Other suggestions?

Shouldn't that just be

ODBC;Driver={Oracle in
Ora920};Host=hostname;Port=1521;SID=thesid;UID=myid;PWD=mypwd;

?
 
V

Vince

Thanx for the reponse.

When I try your suggestion I get:

"[Oracle}{ODBC][Ora]ORA-12560: TNS:protocol adapter arror
(#12560)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
(#0)"


Dirk Goldgar said:
Vince said:
On my Win2k PC I'm trying to execute a pass through query in Access 97
(Access 2000 gives me the same results) per a dsn-less connection
against an Oracle 9.2.0.4 database residing on a unix server.

I keep getting an eror window: "ODBC -- call failed"

In that window I click OK and get another window: "[Microsoft][ODBC
Driver Manager] Data source name not found and no default driver
specified(#0)"


Here's the value in Query Properties|ODBC Connect Str:

ODBC;connect_string = "Driver={Oracle in Ora920}; Host=hostname;
Port=1521; SID=thesid; UID=myid; PWD=mypwd;"

If I remove "UID=myid; PWD=mypwd;" from connect_string, I'm prompted
to select a dsn. If I make a selection, I'm prompted for
userid/password, supply same, and all works.

I've tried numerous drivers, none work. Is something wrong with the
syntax of my connect_string? Other suggestions?

Shouldn't that just be

ODBC;Driver={Oracle in
Ora920};Host=hostname;Port=1521;SID=thesid;UID=myid;PWD=mypwd;

?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dirk Goldgar

Vince said:
Thanx for the reponse.

When I try your suggestion I get:

"[Oracle}{ODBC][Ora]ORA-12560: TNS:protocol adapter arror
(#12560)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed (#0)"

We're getting closer! <g> I don't know what that means, though. Did
the Oracle DBA give you that connection string? I have no experience
connecting to Oracle. All I know is what I read at

http://www.carlprothman.net/Default.aspx?tabid=81
 
L

Lynn Trapp

Vince,

The ORA-12560 is a TNS error that could indicate several things from
problems with the listener to database permissions. Here is a partial quote
of a Metalink article on the error:

ORA-12560 STARTING LISTENER ON NT - TROUBLESHOOTING
---------------------------------------------------

ORA-12560: TNS:protocol adapter error
Cause: A generic protocol adapter error occurred.
Action: Check addresses used for proper protocol specification. Before
reporting this error, look at the error stack and check for lower
level transport errors.For further details, turn on tracing and
reexecute the operation. Turn off tracing when the operation
is complete.

If you have access to Metalink, I would suggest that you do a search for the
following document number -- 118999.1

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Vince said:
Thanx for the reponse.

When I try your suggestion I get:

"[Oracle}{ODBC][Ora]ORA-12560: TNS:protocol adapter arror
(#12560)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
(#0)"


Dirk Goldgar said:
Vince said:
On my Win2k PC I'm trying to execute a pass through query in Access 97
(Access 2000 gives me the same results) per a dsn-less connection
against an Oracle 9.2.0.4 database residing on a unix server.

I keep getting an eror window: "ODBC -- call failed"

In that window I click OK and get another window: "[Microsoft][ODBC
Driver Manager] Data source name not found and no default driver
specified(#0)"


Here's the value in Query Properties|ODBC Connect Str:

ODBC;connect_string = "Driver={Oracle in Ora920}; Host=hostname;
Port=1521; SID=thesid; UID=myid; PWD=mypwd;"

If I remove "UID=myid; PWD=mypwd;" from connect_string, I'm prompted
to select a dsn. If I make a selection, I'm prompted for
userid/password, supply same, and all works.

I've tried numerous drivers, none work. Is something wrong with the
syntax of my connect_string? Other suggestions?

Shouldn't that just be

ODBC;Driver={Oracle in
Ora920};Host=hostname;Port=1521;SID=thesid;UID=myid;PWD=mypwd;

?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
A

aaron.kempf

Access passtrhough and all that crap is to unreliable for real-world
use.

you should convince your company to buy SQL Server; the tools in SQL
are much better at working with oracle than some silly mdb files
 
L

Lynn Trapp

Aaron,
You have no idea about the needs of my company. For our enterprise
applications we use Oracle tools and Toad for working with Oracle. Some of
our departments use Access for various departmental productivity
applications and need to be able to query some Oracle tables. Access works
more than well for their purposes.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
V

Vince

Thanx again for the response. Actually I am the Oracle DBA. I'm trying to
find a solution for one of our developers. We have VB code that works.
But, we've not been able to get a DSN-less pass-through query to work in
MS-Access using the "conventional" method of populating the Properties box
with a connect string. We either get an "ODBC call failed" or a pop-up
permitting us to choose a DSN. Well, we don't want to choose a DSN, we want
a dsn-less connection. I mean we're better off using "DSN=whatever" rather
than a connect_string. This way the user doesn't have to first pick a DSN,
then supply credentials; rather he/she only has to supply credentials. The
customer has agreed to this and I'm sure our Security unit prefers it this
way, since credentials aren't stored anywhere. Out of curiosity though,
I'll continue to play with this; never know when it might come in handy.

Thanx to all for your help.

Dirk Goldgar said:
Vince said:
Thanx for the reponse.

When I try your suggestion I get:

"[Oracle}{ODBC][Ora]ORA-12560: TNS:protocol adapter arror
(#12560)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed (#0)"

We're getting closer! <g> I don't know what that means, though. Did
the Oracle DBA give you that connection string? I have no experience
connecting to Oracle. All I know is what I read at

http://www.carlprothman.net/Default.aspx?tabid=81

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
L

Lynn Trapp

Vince,
This website may provide you with some help.

http://www.accessmvp.com/djsteele/DSNLessLinks.html

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Vince said:
Thanx again for the response. Actually I am the Oracle DBA. I'm trying
to
find a solution for one of our developers. We have VB code that works.
But, we've not been able to get a DSN-less pass-through query to work in
MS-Access using the "conventional" method of populating the Properties box
with a connect string. We either get an "ODBC call failed" or a pop-up
permitting us to choose a DSN. Well, we don't want to choose a DSN, we
want
a dsn-less connection. I mean we're better off using "DSN=whatever"
rather
than a connect_string. This way the user doesn't have to first pick a
DSN,
then supply credentials; rather he/she only has to supply credentials.
The
customer has agreed to this and I'm sure our Security unit prefers it this
way, since credentials aren't stored anywhere. Out of curiosity though,
I'll continue to play with this; never know when it might come in handy.

Thanx to all for your help.

Dirk Goldgar said:
Vince said:
Thanx for the reponse.

When I try your suggestion I get:

"[Oracle}{ODBC][Ora]ORA-12560: TNS:protocol adapter arror
(#12560)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed (#0)"

We're getting closer! <g> I don't know what that means, though. Did
the Oracle DBA give you that connection string? I have no experience
connecting to Oracle. All I know is what I read at

http://www.carlprothman.net/Default.aspx?tabid=81

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
V

Vince

Lynn,

Thanx for the tip. I'll take a look.


Lynn Trapp said:
Vince,
This website may provide you with some help.

http://www.accessmvp.com/djsteele/DSNLessLinks.html

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Vince said:
Thanx again for the response. Actually I am the Oracle DBA. I'm trying
to
find a solution for one of our developers. We have VB code that works.
But, we've not been able to get a DSN-less pass-through query to work in
MS-Access using the "conventional" method of populating the Properties box
with a connect string. We either get an "ODBC call failed" or a pop-up
permitting us to choose a DSN. Well, we don't want to choose a DSN, we
want
a dsn-less connection. I mean we're better off using "DSN=whatever"
rather
than a connect_string. This way the user doesn't have to first pick a
DSN,
then supply credentials; rather he/she only has to supply credentials.
The
customer has agreed to this and I'm sure our Security unit prefers it this
way, since credentials aren't stored anywhere. Out of curiosity though,
I'll continue to play with this; never know when it might come in handy.

Thanx to all for your help.

Dirk Goldgar said:
Thanx for the reponse.

When I try your suggestion I get:

"[Oracle}{ODBC][Ora]ORA-12560: TNS:protocol adapter arror
(#12560)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed (#0)"

We're getting closer! <g> I don't know what that means, though. Did
the Oracle DBA give you that connection string? I have no experience
connecting to Oracle. All I know is what I read at

http://www.carlprothman.net/Default.aspx?tabid=81

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top