Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Access 2003 performance with Linked tables

    Hi,

    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
    Wim

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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...


    HTH

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thanks for the reply.

    It's a sad thing Access 2003 behaves different from Access 2000.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    waht you were getting reasonable performance on A2000/2002 but its degraded significantly on A2003. - thats doesn't sound right. I'd expect similar performance (IE lousy).

    wonder if its a library thing - does the MSDN site mention anything in this context?
    IS it a specific form / form element that is causing the delays?

    it could be that you need to explcitly tell the SQL parser to handle it at the server end not the client end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •