1 2 Previous Next 16 Replies Latest reply on Jan 18, 2007 12:24 AM by Dietmar Aust

    Trying to connect old Access97 Client to an Oracle XE server

    551982
      I'm trying to connect an old Access 97 Client app. to an XE server via ODBC. I don't know if it's possible at all, so far I've not been able to see the server from the "Link Table" in Access. I get ORA-12154.
      The XE Client contains no admin tool for ODBC, in this forum I've seen mentioned that XE ODBC uses "Instant ODBC" that have no setup, but does anybody know if what I'm trying is possible at all?
      And if it is possible; how do I do it?
      Assume I know nothing at all about what I'm doing!

      :-)

      Per
        • 1. Re: Trying to connect old Access97 Client to an Oracle XE server
          392393
          I don't know if it's possible at all
          Yes, it's possible, I do it all the time!

          You need to set up the ODBC connection:
          Start --> Settings --> Control Panel --> Administrative Tools --> Data Sources (ODBC)

          Type in the name for your DSN, then pick the TNSName that you want to connect to (optionally you can type the username/password in the username box to save that). Save it and exit.

          Then from within Access, go to File --> Get External Data --> Link Tables
          Change the select box at the bottom (Files of type) to ODBC Databases and pick your DSN.

          Then log in as the user you have created and it should give you a list of tables to link.

          ~Jer
          • 2. Re: Trying to connect old Access97 Client to an Oracle XE server
            392393
            I forgot to mention: For this to work you need to set up your TNSName to the database first (and that should solve your TNS-12154).

            ~Jer
            • 3. Re: Trying to connect old Access97 Client to an Oracle XE server
              Dietmar Aust
              Per,

              are you connecting to the XE instance on the same machine or on a different machine?

              If it is on a different machine, did you install the XE client on the machine you run Access97 on ?

              Regards,
              ~Dietmar.
              • 4. Re: Trying to connect old Access97 Client to an Oracle XE server
                392393
                I've done it both ways, but I was talking about the same machine.

                If I'm going to run Access from a different machine, I just use the instantclient with instantclient-odbc and the setup goes pretty much the same way (with the exception of configuring the tnsnames.ora file).

                ~Jer

                EDIT: Sorry Deitmar, thought "Per" was a typo with the intention to reference my name before I read the OP's post...

                Message was edited by:
                Jer
                • 5. Re: Trying to connect old Access97 Client to an Oracle XE server
                  Dietmar Aust
                  Hi Jer,

                  I am more than positive that you know how to make it work :).

                  As you know, tnsnames.ora isn't available in the XE client out of the box, you have to set it up or use the easy connect syntax either.
                  It confused me at first, too. This is why we need to know from the OP (Per that is ;).

                  Greetinx from Cologne,
                  ~Dietmar.
                  • 6. Re: Trying to connect old Access97 Client to an Oracle XE server
                    551982
                    I don't know if it's possible at all
                    Yes, it's possible, I do it all the time!

                    You need to set up the ODBC connection:
                    Start --> Settings --> Control Panel -->
                    Administrative Tools --> Data Sources (ODBC)

                    Type in the name for your DSN, then pick the TNSName
                    that you want to connect to (optionally you can type
                    the username/password in the username box to save
                    that). Save it and exit.

                    Then from within Access, go to File --> Get External
                    Data --> Link Tables
                    Change the select box at the bottom (Files of type)
                    to ODBC Databases and pick your DSN.

                    Then log in as the user you have created and it
                    should give you a list of tables to link.

                    ~Jer
                    Yes, it should, but it doesn't. The reason I' trying this stunt, is because a 8i server becomes unavailable. And as the database I'm running is pretty small, I migrated it to an Express server. The setup before was Oracle ODBC driver 2. 5 (For Oracle 7.34, no less) and it has worked flawlessly for many years.
                    What confuses me, is that the normal connection procedure doesn't work. Also, I know that Net8 has taken over for SQLNet, and I thought that may be the problem. (Not sure if Net8 actually uses TNSNames)
                    Also, Access 97 uses the old Microsoft Jet engine Object model, DAO, I thought that may be a problem.
                    At the moment I've managed to connect by using the "Microsoft for Oracle" OCBC Driver on Microsoft XP. It may cause other problems with my login to the database methode, but it is managable. I really would like to know why the easy and straightforward doesn't work, though...
                    • 7. Re: Trying to connect old Access97 Client to an Oracle XE server
                      551982
                      I'm connecting from a diffewrent machine.
                      • 8. Re: Trying to connect old Access97 Client to an Oracle XE server
                        551982
                        I have a TNSNames.ORA that I set up with the original driver. (2.5 for Oracle 7.34, as mentioned above.) I used EasyConfig in that Client installation to set up the new TnsName entry as you described above. My old TNSPing returns Ok, but Access 97 refuses to play.
                        All this is with the XeClient installed.
                        As mentioned above, I've now uninstalled the XEClient and uses Microsoft for Oracle (Standard Issue with XP SP2)
                        In the meantime I've also installed Net8, I saw somewhere I needed the Net8 Admin program to set up the new ODBC drivers. (Net8 have proved impossible to uninstall...)
                        • 9. Re: Trying to connect old Access97 Client to an Oracle XE server
                          392393
                          Can you connect from sqlplus? It should give you a more meaningful response than tnsping. You can't use an Oracle 7 client to connect to a 10gR2 database, if that's what you're trying to do. Have you tried using the instantclient with ODBC? I'm not even sure the XE Client comes with an ODBC driver included.

                          ~Jer
                          • 10. Re: Trying to connect old Access97 Client to an Oracle XE server
                            392393
                            This is why we need to know from the OP (Per that is ;).
                            I agree. I think the mash of multiple clients is causing some issues... have you tried the easy connect syntax with the odbc driver? Where do you insert the info?

                            ~Jer
                            • 11. Re: Trying to connect old Access97 Client to an Oracle XE server
                              Dietmar Aust
                              Hi Jer,
                              I agree. I think the mash of multiple clients is
                              causing some issues... have you tried the easy
                              connect syntax with the odbc driver? Where do you
                              insert the info?
                              Right, I think so, too. Using the easy connect syntax he shouldn't have to worry about the different clients and different tnsnames.ora which could be used.

                              I have written up a "guided tour" on how to set up the ODBC connection for the XE Client in my Blog, I couldn't find anything detailed in the documentation. It is a bit different from the "usual" Oracle setup procedure.

                              http://daust.blogspot.com/2007/01/xe-odbc-connection-to-remote-xe-server.html

                              Greetinx,
                              ~Dietmar.
                              • 12. Re: Trying to connect old Access97 Client to an Oracle XE server
                                551982
                                The link to the blog did the trick. I had missed the fact that the data from TNSCONFIG should be crammed into the ODBC admin DBQ field. Having done that, the rest is easy, just a lot of work.
                                One sigh at the end on the solution, though: There are several hundred pass-through queries in my Access Client. Each have an ODBC connect string. These connect strings now all contain the name of the server. There is, I'm informed, no automated way of changing the PT-query ODBC-connect string, except by hand. I'm currently running on a test server...
                                But I consider my current problem solved.
                                Thanks, folks!

                                :-)

                                Per
                                • 13. Re: Trying to connect old Access97 Client to an Oracle XE server
                                  Dietmar Aust
                                  Hi Per,

                                  please wait before changing everything to the new syntax.

                                  There are ways to make the XE Client (which is derived from the Instant Client) use the tnsnames.ora file.

                                  I will post the instructions shortly.

                                  Regards,
                                  ~Dietmar.
                                  • 14. Re: Trying to connect old Access97 Client to an Oracle XE server
                                    392393
                                    Dietmar,

                                    Thanks for the link! That answers the question I was looking for, that you can use EasyConnect syntax in the TNS box. Useful info to have.

                                    Also, just an FYI: you can hardcode the password in the userid box. It's plain text, but if you're using an app that does application authentication and you're just setting up user DSNs it makes it easier on a dev box. Just use hr/hr instead of hr and you don't get the login prompt :)

                                    ~Jer
                                    1 2 Previous Next