| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Access97 works with SQL-Server not Oracle
On Mon, 29 Jun 1998 12:50:48 -0700, Troy Perchotte <max_at_headroom.com> 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
 |
 |