From: jkallman@us.oracle.com (Joel R. Kallman) Subject: Re: Access97 works with SQL-Server not Oracle Date: 1998/06/29 Message-ID: <35990654.7070596@newshost.us.oracle.com>#1/1 Content-Transfer-Encoding: 7bit References: <3597F017.BBC1B71A@headroom.com> Content-Type: text/plain; charset=us-ascii Organization: Oracle Corporation Mime-Version: 1.0 Newsgroups: comp.databases.oracle.tools,comp.databases.oracle.misc On Mon, 29 Jun 1998 12:50:48 -0700, Troy Perchotte wrote: >A query that contains a sub-query all based on a single attached table, >and listed below, only seems to work properly when using tables that are >in Access or SQL-Server. When attached to the exact same table in >Oracle, I do not receive any records back from my query. > >Since I don't have a SQL-Server database, a Microsoft tech tested my >problem, with the correct results. Oracle techs were unwilling to test >the problem. > I am an Oracle employee. Here I am.... >I am using Oracle 8.04 on a NT 4 (SP3) PC. The ODBC driver is >3.0.28.22. > >I am not sure if this is a problem is in Access, ODBC, Oracle, or >whether it is something local to my system. Please let me know if this >happens at other simular setups. Any suggestions would be appreciated. >-------- I think this might be a problem with your ODBC driver or your local system. >1. from a new Access97 database create a table called TEST with the >following >structure: > >Field Name: "ID" Data Type: "Long Integer" Index: "No Duplicates" >Field Name: "NAME" Data Type: "Text" > >2. populate about 3 records with junk data. >3. export the table to Oracle as "TEST_ORACLE" and also to the >Northwinds database (or any other Access97 database) as "TEST_ACCESS". >4. link the remote tables to your database. > I successfully did steps 1 through 4. >5. create the following sub-query and save it as "x2" > >SELECT TEST.NAME >FROM TEST >WHERE (((TEST.NAME)="99")); > >Note: "99" should not be present in any of the populated records. > I did this step, although, since it is not used below, I counldn't figure out why you wanted this performed. Maybe it was just to perfectly recreate the steps of your scenario. >6. create the following queries. save the first one as "z1" and the >second as "z2": > >SELECT [TEST_ACCESS].Name >FROM [TEST_ACCESS] LEFT JOIN x2 ON [TEST_ACCESS].Name = x2.NAME; I saved this in the default Northwinds database. > >SELECT TEST_ORACLE.NAME >FROM TEST_ORACLE LEFT JOIN x2 ON TEST_ORACLE.NAME = x2.NAME; >--------- By default, Access prepends the Oracle schema owner to the table name, so for my queries, replace "TEST_ORACLE" with "SCOTT_TEST_ORACLE". >That's it. > >If you run the "z1" query, you should see the NAME records that you >entered - this is correct. This query, against the external data from the Northwinds database, was correctly returned. >If you run the "z2" query, you should see nothing returned - this is >obviously NOT correct. This query, against the external data from the Oracle database, was also correctly returned. This IS correct. I did this using MS Access 97 and Oracle8 8.0.4 on Solaris. The ODBC driver is 8.0.4.3.0 (developed by Oracle). You can grab the Oracle-developed ODBC driver for free from our web site (at http://www.oracle.com) to try and isolate the problem a little further. > >Let me know if this works or doesn't work for you. > >Thanks in advance. > >Troy Perchotte > Thanks! Joel Joel R. Kallman Oracle Government, Education, & Health Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com ---- The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation.