Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

linked Oracle tables and access indexes

Status
Not open for further replies.

THEaix

MIS
Jun 7, 2007
30
US
I have a Access(2003) DB with linked tables to a Oracle 10g DB thru a ODBC connection. There are no indexes built on the Access side but the Oracle Db has all the appropriate indexes on it. All the Access is used for is queries to the Oracle Db and users build these queries in access.
I just learned how to spell Access and was given this to admin.. While building a test 2007 Access Db I linked a table and Access asked for me to setup a Primary key. But the Oracle table has all of these already.
That got me looking around the forum for this and then I found Threads refering to ACCESS INDEXES built on linked ODBC Oracle tables.
When they set this Access Db up did someone miss the boat on setting primary keys in Access and indexes on the linked Oracle tables?
The queries run, although some do timeout. I just blamed it on the Access and ODBC connections not being robust enough to handle large returns.

Thanks
 
I don't think there is a reason to create an index on the Access side unless you need to edit the data in which case you need a unique index. I could be very wrong...

Have you tried using pass-through queries? These would be much more efficient. They can involve only data from the Oracle server.

Duane
Hook'D on Access
MS Access MVP
 
No I havn't looked at Pass-through queries. I'll will do a little research on that.

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top