Access97 works with SQL-Server not Oracle

[Quoted] [Quoted] A query that contains a sub-query all based on a single attached table, [Quoted] and listed below, only seems to work properly when using tables that are [Quoted] 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.

[Quoted] 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.

[Quoted]
1. from a new Access97 database create a table called TEST with the following [Quoted] 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. [Quoted] [Quoted] 3. export the table to Oracle as "TEST_ORACLE" and also to the Northwinds database (or any other Access97 database) as "TEST_ACCESS". [Quoted] 4. link the remote tables to your database.

[Quoted] [Quoted] 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.

[Quoted] [Quoted] 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;

SELECT TEST_ORACLE.NAME
[Quoted] [Quoted] FROM TEST_ORACLE LEFT JOIN x2 ON TEST_ORACLE.NAME = x2.NAME;

[Quoted] [Quoted] If you run the "z1" query, you should see the NAME records that you entered - this is correct.
[Quoted] [Quoted] If you run the "z2" query, you should see nothing returned - this is obviously NOT correct.

[Quoted] Let me know if this works or doesn't work for you.

Thanks in advance.

Troy Perchotte Received on Mon Jun 29 1998 - 00:00:00 CEST