ODBC Table Problem

[KerryL]

Using an ODBC configuration, I've linked my Access application to an Oracle database. The Oracle database is fairly large.

Though I can open the table up, when I try and do any querying against it Access locks up and fails to respond. Even if I restrict the query results to a small number of records Access stops responding. I then have to go into my task list to shut it down.

So far I haven't been able to figure out why. Any ideas?

Kerry

 

[CosmoKramer]

Kerry,

Check the ODBC Timeout property for your query. By default it's set to 60 (seconds). Try increasing the number, or set it to 0 for no timeout.....

 

[Databaseguy]

I've got linked tables with about 300,000 or so records. It takes forever to load. The problem is, when you open the table, Access likes to get all of the records in the table. Even if you restrict the records by using a query, Access still goes through the entire recordset while you sit and wait.(Yawn). It's actually doing something, it just down't act like it is.

I don't know Oracle (yet), but I do know with SQL a better solution is to base your form or whatever on a SQL statement and hand it to the server. SQL server processes the info at the server and returns only the records from the SQL statement in lieu of the entire table.

If oracle doesn't have this capability, the only other option I can offer is make sure that your Linked Oracle table has one or more indexes that Access recognizes. This might speed things up a bit, as well as insuring that your linked table is updateable.