developers

    Forum Stats

  • 3,873,737 Users
  • 2,266,635 Discussions
  • 7,911,626 Comments

Discussions

Access front end Error

737578
737578 Member Posts: 8
edited Jan 15, 2010 6:31AM in General Database Discussions
I''m using MS Access as a simple application to access engineering back-end Oracle information to run reports on, etc. This is deployed to the users via a network share.

The application probably uses about, say, 15 SQL Pass Through queries (Using the Microsoft ODBC for Oracle Driver) to access the relevant data on an Oracle 9i Database.

Strangely, all the SQL Pass Through queries work (With exactly the same connection string), apart from one! When I try to access this from their client machines I get;

""
ODBC--Call failed.

(Microsoft)(ODBC driver for Oracle)(Oracle)ORA-00511: Message 511 not found; product=RDBMS; facility=ORA(#511)

""

All the other Pass Through's work fine, and on the Admin Client where I build the Access Application it works fine too. I'm stumped as to what the problem could be.

Any help would be greatly appreciated.

Luke

Edited by: user12282018 on Jan 15, 2010 12:51 AM
«1

Answers

  • ioan
    ioan Member Posts: 132
    Hi,

    oerr tns 00511
    00511, 00000, "No listener"
    // *Cause: The connect request could not be completed because no application
    // is listening on the address specified, or the application is unable to
    // service the connect request in a sufficiently timely manner.
    // *Action: Ensure that the supplied destination address matches one of
    // the addresses used by the listener - compare the TNSNAMES.ORA entry with
    // appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to go
    // by way of an Interchange. Start the listener on the remote machine.


    Ioan
    ioan
  • 737578
    737578 Member Posts: 8
    ioan wrote:
    // *Action: Ensure that the supplied destination address matches one of
    // the addresses used by the listener - compare the TNSNAMES.ORA entry with
    // appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to go
    // by way of an Interchange. Start the listener on the remote machine.
    Hi Ioan,

    What I don't understand is that another SQL query, to the same database, will work with exactly the same connection string applied. In fact it's not even a big table that it's failing on, other queries work bring back > 10,000 records - I've compared the tnsnames.ora files on the client that works and the one that doesn't (even copied them over) to no effect. Still stumped.

    Luke
  • Satish Kandi
    Satish Kandi Member Posts: 9,627 Gold Trophy
    Does reconfiguring of the ODBC connection help?
    Satish Kandi
  • ioan
    ioan Member Posts: 132
    Hi,

    do you have 2 tnsnames.ora files? Are we talking about different clients (machines)?

    Ioan
  • 737578
    737578 Member Posts: 8
    I could try using a different ODBC connection I suppose ... ?? But the connection is defined as a string in the properties of the SQL Pass Through query, how would I reconfigure it?? Not quite sure what you mean (I don't mean to be thick on purpose, just not a real expert in this field)

    Thanks!

    Luke
  • 737578
    737578 Member Posts: 8
    ioan wrote:
    Hi,

    do you have 2 tnsnames.ora files? Are we talking about different clients (machines)?
    Yes different local desktops if you like.

    I have used one (say, Machine A) to build the application, all the queries work on that machine. The application sits on a network share. If I go down to the other local desktop (say, Machine B) it fails with the error message shown. I have taken the TNSNAMES.ora file from Machine A and overwritten the one on Machine B, but this makes no difference.

    Luke
  • Satish Kandi
    Satish Kandi Member Posts: 9,627 Gold Trophy
    To be very frank, I have quite literally no idea as of now about "SQL Pass through query".

    But to my knowledge, for MS Access to connect to Oracle database, one needs to configure ODBC connection. I was referring to this ODBC connection which can
    1. either be reconfigured
    2. dropped and recreated

    You can find information on internet about how to perform these two operations.
  • 737578
    737578 Member Posts: 8
    edited Jan 15, 2010 5:42AM
    Satish Kandi wrote:
    To be very frank, I have quite literally no idea as of now about "SQL Pass through query".

    But to my knowledge, for MS Access to connect to Oracle database, one needs to configure ODBC connection. I was referring to this ODBC connection which can
    1. either be reconfigured
    2. dropped and recreated

    You can find information on internet about how to perform these two operations.
    Purely out of interest, an SQL Pass Through query is just an MS Access query object at the end of the day, different from a native MS Access query in that it is written only in SQL from the interface (i.e. you don't build the query up in a graphical interface) to simply pass through an external datasource and bring back results at that moment in time. The ODBC connection, as stated, is defined within the properties of the query - in this instance this is the connection string ...

    odbc;Driver={Microsoft ODBC for Oracle};Server=XXX;Uid=USER_NAME;Pwd=PASSWORD

    So in terms of an ODBC datasource that you build up and sit on your machine (.dsn for e.g.) it doesn't use it in that sense. It uses the driver to initiate an ODBC connection each time with the Server, UID and PWD supplied.

    You have given me the idea to use a different driver to see if that might be the problem, but it terms of reconfiguring and/ or dropping and recreating a datasource to use then it doesn't quite work like that in this instance (IMHO).

    Thanks!

    Luke

    Edited by: user12282018 on Jan 15, 2010 2:42 AM
  • Satish Kandi
    Satish Kandi Member Posts: 9,627 Gold Trophy
    Thanks for the information. :-)

    Could you post the sample queries here? One that works and one that does not work?
  • ioan
    ioan Member Posts: 132
    edited Jan 15, 2010 6:09AM
    If you have to machines, then try tnsping from the one, where the connection is not working. Also try and see if you see (ping) the server where the listener resides, check if there are no network issues between the client and server (any access lists in place) ?

    Ioan

    Edited by: ioan on 15.1.2010 3:08
This discussion has been closed.
developers