HELP!! ACCESS ODBC ORACLE

HI,

I have a problem in MS-ACCESS. The setting is the following:

I have an application developed in ms-access, which has tables attached to a database in Oracle 7. I am using ODBC (which comes with access 2.0) and am using Chameleon as my TCP-IP network interface.

Tables of interest are two:

Table1 Name: TASK
Table2 Name: INVESTIGATOR

Fields of interest in these Tables:

TASK: ID1 => Number

       Date1 => Date / Time

INVESTIGATOR: ID1 => Number   

I have a query in ms-access, which performs a join on two tables and pulls out certain number of records depending on a criteria which goes like this:

Join the two tables on ID1 and restrict the records by TASK.Date1

Format(TASK.Date1, "yyyy") > (Year(Now()) - 1)

The above query is in MS-Access using QBE grid.

Now if I attach TASK table from Oracle and keep the INVESTIGATOR table in MS-Access (as the applications own table), On running the query it brings out the records and everything executes perfectly.

But....... If I have both of these tables in ORACLE and attach them in Access. On running the query the following happens:

(1) The hard disk starts trashing.

(2) After about 5 minutes or so, SOMETIMES I get some records in the query

    datasheet view. But still the hard disk is being trashed. I cannot do     anything.

(3) If I am extremely lucky, I get to go to the last record (from the records

    menu). But then as soon as I again say to go to the first record,     It gives me some weird error. And fills all the fields with "#Error".

A list of all the error that I get (depending on my PC's wish) are as follows:

(1) ODBC Conformance Specification Error (-7711). Please contact the ODBC

    driver vendor.

(2) In a System Modal Message Box, I get:

    "SStars caused a protection fault in some module"     After this error I have to hard reboot my PC.

If someone can please help me out, why all this problems are occurring, or give me some suggestions I would really be obliged. Otherwise after trying all the Customer Support lines of Oracle and Microsoft, the only solution I am thinking now is to contact some PC Vodoo Doctor (I think my PC is possessed by some evil ODBC spirit).

Thanks a lot.

Alay Desai

 Phone:  (908) 940-8814     Home
         (908) 423-7413     Work

 email: aldesai_at_caip.rutgers.edu
        aldesai_at_paul.rutgers.edu