Unanswered: Access 2003 performance with Linked tables
We're moving from Access 2000 to 2003. One of our Access applications serves as a front end: one of the tables in ACCESS is a linked table to the DB2 database via ODBC.
If I try a search in the form, I have to wait half a minute for the result, I am connected to the DB2 server via LAN. People who are connected to our DB2 server via WAN have to wait 30 minutes or longer. It seems Access 2003 reads the whole table and performs the search on the client PC instead of letting DB2 do the search on the server. Access 2000 was able to use the indexes defined on the table in DB2 (search took 1 or 2 seconds). How can I convince 2003 to use DB2's indexes too ?
Any suggestions ?
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Sorry there is no easy way to say it ither than redesign you application to use pass through queries and unbound controls. Effectively you are getting DB2 to act as your data store, but then do all the processing on local copies of the data, so its a loose loose combination
There is no other way round it, but you have start from scratch - its a different design paradigm, not more easy form design, you have to be much more carefull about record locks, error trapping etc...