Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014

    Unanswered: Access 2013-ODBC-DB2 Not Updating Table

    Access 2013 is linked to our AS400 (DB2) via ODBC. When a change is made on the AS400, the linked Access table is not updating. The only way I'm able to see the new information is to press "Refresh" in Access which reloads the entire table. At over 80,000 records this takes considerable amount of time (approx. 1-3 minutes) when done several times a day (<50). I've tried several things:

    Changing update and refresh intervals to anywhere from 10-30 seconds.
    Turning off Name Autocorrect

    Trying all connection types (ODBC access mode, access mode w\ call allowed, etc.)

    My questions:
    1) Shouldn't the Access table pull in the new information after its been added to the AS400?
    2) If no to question 1; How do I pull in just the most recent instead of having to reload the entire table which takes entirely too long? Or is there are better way that I'm just not seeing.

    Thank You,

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    how do you think Access would know through ODBC that the underlying data has changed
    . what you could do is be a bit more savvy about how you consumje the data.
    and do a read of the required subset of the data from the AS400 when you need to use it and not bring over the whole subset

    as we dont knwo what the query you are usign is its tricky to advise

    but it may involve extracting, say, ass sales made in the last hour
    all ales of product X,Y or Z in the current week
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2014

    Addl. Information

    Let me clarify. The query is a basic select on all records (Customer Quotes) containing 8 fields. Per the COO, all data must be entered into the AS400 only, and Access is just for printing reports and checking data (users don't want to use green screen). Therefore, data may be new and or changed throughout the day. What I was looking for was a continual two way "link" so that the Access Table would see the Selects, Creates, Inserts, etc. from the DB2.

    As for a subset, I will ask our DB2 admin to make a field for the customer quote "Date Changed" and update data based on that field. Does that sound appropriate?

Tags for this Thread

Posting Permissions

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