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?

"Vince" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> 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)

Thanx for the reponse.

When I try your suggestion I get:

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


"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Vince" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)
> > 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)
>
>

"Vince" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)
> Thanx for the reponse.
>
> When I try your suggestion I get:
>
> "[Oracle}{ODBC][Ora]ORA-12560: TNSrotocol 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)

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: TNSrotocol 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/conrad...essjunkie.html



"Vince" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Thanx for the reponse.
>
> When I try your suggestion I get:
>
> "[Oracle}{ODBC][Ora]ORA-12560: TNSrotocol adapter arror
> (#12560)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
> (#0)"
>
>
> "Dirk Goldgar" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> "Vince" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)
>> > 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)
>>
>>
>
>

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

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/conrad...essjunkie.html



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> "Vince" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)
> > Thanx for the reponse.
> >
> > When I try your suggestion I get:
> >
> > "[Oracle}{ODBC][Ora]ORA-12560: TNSrotocol 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)
>
>

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/conrad...essjunkie.html



"Vince" <(E-Mail Removed)> wrote in message
news:%23$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> "Vince" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)
>> > Thanx for the reponse.
>> >
>> > When I try your suggestion I get:
>> >
>> > "[Oracle}{ODBC][Ora]ORA-12560: TNSrotocol 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)
>>
>>
>
>

Lynn,

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


"Lynn Trapp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/conrad...essjunkie.html
>
>
>
> "Vince" <(E-Mail Removed)> wrote in message
> news:%23$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> >> "Vince" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)
> >> > Thanx for the reponse.
> >> >
> >> > When I try your suggestion I get:
> >> >
> >> > "[Oracle}{ODBC][Ora]ORA-12560: TNSrotocol 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)
> >>
> >>
> >
> >
>
>