Access pass-through query using dsn-less connection not working "Vince" <vincent.rineer[ at ]capbluecross.com> 30.01.2006 19:12:36 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?

Re: Access pass-through query using dsn-less connection not working "Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com> 30.01.2006 19:39:17 "Vince" <vincent.rineer[ at ]capbluecross.com> wrote in message news:uph1iEdJGHA.3100[ at ]tk2msftngp13.phx.gbl [Quoted Text] > 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)

Re: Access pass-through query using dsn-less connection not working "Vince" <vincent.rineer[ at ]capbluecross.com> 30.01.2006 19:59:32 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" <dg[ at ]NOdataSPAMgnostics.com> wrote in message news:O4FnxSdJGHA.2828[ at ]TK2MSFTNGP12.phx.gbl... [Quoted Text] > "Vince" <vincent.rineer[ at ]capbluecross.com> wrote in message > news:uph1iEdJGHA.3100[ at ]tk2msftngp13.phx.gbl > > 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) > >

Re: Access pass-through query using dsn-less connection not working "Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com> 30.01.2006 20:49:29 "Vince" <vincent.rineer[ at ]capbluecross.com> wrote in message news:%235UazedJGHA.668[ at ]TK2MSFTNGP11.phx.gbl [Quoted Text] > 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)

Re: Access pass-through query using dsn-less connection not working "Lynn Trapp" <ltrapp_NOSPAM[ at ]ltcomputerdesigns.com> 31.01.2006 14:56:05 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" <vincent.rineer[ at ]capbluecross.com> wrote in message news:%235UazedJGHA.668[ at ]TK2MSFTNGP11.phx.gbl... [Quoted Text] > 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" <dg[ at ]NOdataSPAMgnostics.com> wrote in message > news:O4FnxSdJGHA.2828[ at ]TK2MSFTNGP12.phx.gbl... >> "Vince" <vincent.rineer[ at ]capbluecross.com> wrote in message >> news:uph1iEdJGHA.3100[ at ]tk2msftngp13.phx.gbl >> > 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) >> >> > >

Re: Access pass-through query using dsn-less connection not working "aaron.kempf[ at ]gmail.com" <aaron.kempf[ at ]gmail.com> 31.01.2006 18:38:57 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

Re: Access pass-through query using dsn-less connection not working "Lynn Trapp" <ltrapp_NOSPAM[ at ]ltcomputerdesigns.com> 31.01.2006 19:30:31 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 <aaron.kempf[ at ]gmail.com> wrote in message news:1138732737.043000.67210[ at ]z14g2000cwz.googlegroups.com... [Quoted Text] > 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 >

Re: Access pass-through query using dsn-less connection not working "Vince" <vincent.rineer[ at ]capbluecross.com> 02.02.2006 14:08:27 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" <dg[ at ]NOdataSPAMgnostics.com> wrote in message news:%23BshB6dJGHA.3000[ at ]TK2MSFTNGP14.phx.gbl... [Quoted Text] > "Vince" <vincent.rineer[ at ]capbluecross.com> wrote in message > news:%235UazedJGHA.668[ at ]TK2MSFTNGP11.phx.gbl > > 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) > >

Re: Access pass-through query using dsn-less connection not working "Lynn Trapp" <ltrapp_NOSPAM[ at ]ltcomputerdesigns.com> 02.02.2006 14:45:57 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" <vincent.rineer[ at ]capbluecross.com> wrote in message news:%23$okoIAKGHA.1848[ at ]TK2MSFTNGP12.phx.gbl... [Quoted Text] > 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" <dg[ at ]NOdataSPAMgnostics.com> wrote in message > news:%23BshB6dJGHA.3000[ at ]TK2MSFTNGP14.phx.gbl... >> "Vince" <vincent.rineer[ at ]capbluecross.com> wrote in message >> news:%235UazedJGHA.668[ at ]TK2MSFTNGP11.phx.gbl >> > 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) >> >> > >

Re: Access pass-through query using dsn-less connection not working "Vince" <vincent.rineer[ at ]capbluecross.com> 09.02.2006 19:38:30 Lynn, Thanx for the tip. I'll take a look. "Lynn Trapp" <ltrapp_NOSPAM[ at ]ltcomputerdesigns.com> wrote in message news:uji2hdAKGHA.1132[ at ]TK2MSFTNGP10.phx.gbl... [Quoted Text] > 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" <vincent.rineer[ at ]capbluecross.com> wrote in message > news:%23$okoIAKGHA.1848[ at ]TK2MSFTNGP12.phx.gbl... > > 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" <dg[ at ]NOdataSPAMgnostics.com> wrote in message > > news:%23BshB6dJGHA.3000[ at ]TK2MSFTNGP14.phx.gbl... > >> "Vince" <vincent.rineer[ at ]capbluecross.com> wrote in message > >> news:%235UazedJGHA.668[ at ]TK2MSFTNGP11.phx.gbl > >> > 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) > >> > >> > > > > > >