Linking Oracle tables to Microsoft Access 2000 --Better way?

[tsimoes]

Hi. I am creating a Microsoft Access 2000 application which links, via ODBC, Oracle tables. Using Access to query small tables in Oracle is fine, but some of the tables I have linked contain millions of records, and I get ODBC timeout errors when I try to query these tables.

I thought that if I created pass-through queries, I could avoid these errors, but this has failed as well.

Is there another way to link or retrieve the Oracle table's information to my Access 2000 database and gain the benefit of Oracle's speed? I read about ADO, but I am not totally familiar with this concept, so of course, I need to read up on it. But in the mean time....

Say, for example, the Oracle database has a table named CUSTOMER_RECEIPTS. Now suppose I have linked this table in my Access database. If I try to query the table, like this:

"SELECT RECEIPT_ID, CUSTOMER_ID, CUSTOMER_LAST_NAME
FROM CUSTOMER_RECEIPTS
WHERE (RECEIPT_EFFECTIVE_DATE>='06-JUL-2001' AND RECEIPT_EFFECTIVE_DATE<='20-JUL-2001')&quot;

with a pass-through query, this still is EXTREMELY slow, and I get ODBC time-out errors most of the time.

Is there a better way to link my database to an Oracle database's tables, and get the results? And if so, what are the step-by-step instructions to do this?

I appreciate all the help I can get, and thanks to anyone in advance.

 

[hsitz]

I have zero experience with Oracle, so take this for what it's worth.

But have you checked the same Oracle queries from a different front end to make sure they're not just as slow as from Access?

You can change the ODBC time length before timeout, if the sole problem is that it's taking too long. But you'd also want to find ways to make the query faster in Oracle.

Is the date field in the where clause an indexed field in Oracle? -- Herb

 

[fredl]

The problem is looking at all those records, if you want to filter the records, let oracle do the work not access. There are two ways that I know of create a SQL statement (query) and connect to that, or create a view and connect to that, I do not know which one is faster. But I know from experience that you do not want to download all the records to the windows box. access chokes.
What platform is the oracle sitting on?

fl

 

[B827]

A far better way than linking tables and query them is to use a PASS THROUGH QUERY. This will return only the records matching the query request. Unfortunately you must use ORACLE SQL so using the SQL from existing ACCESS queries doesn't always work.

 

[tsimoes]

I just wanted to take a moment to say thank you, to all who've responded to my question. It is sincerely appreciated.

I've used pass-through queries, and they are fine, but I was hoping there is perhaps another (better?) way to link to Oracle via Access. Unfortunately, even pass-through queries are slow. I just find it difficult to believe that Oracle retrieves information from its own tables so slowly.

So again, if anyone knows of a better way to retrieve information from Oracle using Oracle's speed, please, please let me know.

Thanks!

 

[JUANCARLOS]

You can also try a proyect Access.

JCHR

 

[UKBS]

Used a pass-through query and ensure that the fields you are searching on are indexed.

 

[B827]

The time has several causes but it is best to bring only the records required back from Oracle, as ukbs says searching on indexed fields with precise criteria should be reasonably efficient. Have you tried tuning your SQL directly in Oracle then using it from Access? You should be able to see if Access adds any delays. I have found that referring to an Oracle expert with knowledge of the particular database for help in writing the SQL can improve the performance considerably.

 

[tsimoes]

Just to respond...

Thank you all for responding with your advice. I've decided to take the Visual Basic route, and use pass-through queries as needed. This has been sufficient and quick for most purposes.

Thanks again!