Unanswered: Performance in Access with Linked tables
I am making changes to an ACCESS application which serves as a front end for an oracle database. Most of the tables in ACCESS are linked tables to the oracle database via ODBC.
If I try and make inserts in some larger linked tables, this can take up to a minute for 1 record. ACCESS probably does a full table scan.
If I then replace this linked table, with another table with the same date, but directly imported in ACCESS, the application performs the same tasks in less than a second.
I've been trying to create the MSYSCONF table, to change the default fetch performance for the linked tables, but without effect.
I know ACCESS has problems using the indexes created in oracle, it uses them in alphabetical order. But I can't start changing them all in oracle, just because ACCESS can't handle them ?
If You using Access 97 as your font end , then this is a problem but which can be solve If u use the [ Pass Thought ] Query to Access to your remote database . I Suggest you try to upgrade to Access2k which have a new database font-end project call ADP .