Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Table Updates (Access XP)

    I am using an Access front end for a SQL database. One of the tables I'm using does not appear to automatically be refreshing, if you open the table in Access it has different values to that in the SQL database until you click on Records and Refresh. Is there anyway that code can be written within a form to refresh the table entries to ensure that the data you are showing is absolute and accurate.

    I could just be missing something simple but I can't figure it out, please can someone help??!!! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Table Updates (Access XP)

    Me.Refresh
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Table Updates (Access XP)

    THis worries me, because I thought that linked tables were always refreshed (depending upon your "refresh interval" ) -- is it a Microsoft SQL database that you are linking to?
    thx
    Pat

  4. #4
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Table Updates (Access XP)

    Yes it is, and so did I!!

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Linked Table Updates (Access XP/97)

    Charlotte,
    Do I understand this right? If you refresh a form (to which the Me.Refresh refers, I suppose), all underlying controls & row/recordsources and all links to linked tables for those data update automatically too? (Sounds great <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    I have a data management form with a list based on a table linked to a txt-file. The txt-file is built by another program; which is executed just before the form opens sounds a bit messy maybe, I know, but...) and I need to be sure that (or at least know if) the list box in the form presents the current situation.
    Hasse
    ps In case it's relevant: I use Access 97...

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Table Updates (Access XP)

    linked tables are refreshed automatically. However, the refresh interval isn't "immediately". Me.Refresh just refreshes the recordset for the form, it doesn't requery any controls, etc. Listboxes and Comboboxes need to be requeried to get the latest records to show up, and depending on how the record was added to the source table for the combo or listbox, you may need to refresh the table itself before requerying the control if you want the new item to show up right away.. If you use a DAO recordset to add an item to the combo or listbox, you will see a difference depending on whether you use CurrentDb or DBEngine(0)(0) to create the recordset.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Linked Table Updates (Access XP)

    Thanks for providing the info Charlotte. It's all clear now!

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Table Updates (Access XP)

    Thanks, Charlotte (I'm trying to get my thanks before the message thread becomes a dinosaur, this time).
    I do know about refreshing a form or requering subdata for controls or combo boxes, it's just that this sounded like the tables themselves fell out of sync -- perhaps I ought to ask Beany how _far_ out of sync this data gets ( a couple of minutes, a few hours, etc.).
    The reason for my concern is that a couple of divisions in my place of work have reported having to refresh the tables each and every time "something changes." These are ODBC databases outside of the network, actually, so I don't see where the changes would require them to re-link. Short of me being allowed to go out and visit them, I can't tell if the real problem is what they are reporting, but I was fairly dismissive of their problem <g>. After Beany's message and your response, though, I worried and began a search on the web to see if there were any requirements to refresh table links, and found one issue that seemed specific to ADOX (Pat edit: not FoxPro, as I prev. wrote if anyone read it -- and not w/ data falling out of sync, but falling into "read-only" status instead (KB276035 which I suddenly cannot find again)) but anything else that mentioned it seemed to indicate that tables did not have to be relinked for fresh data. Anyway, I won't worry a whole lot more.

    > If you use a DAO recordset to add an item to the combo or listbox, you will see a difference depending on whether you use CurrentDb or DBEngine(0)(0) to create the recordset.

    That's interesting! I skimmed an article in the Access VB magazine a couple of years ago, that said that currentdb() was faster, so I abandoned the dbentgine(0)(0) format. Well, there's certainly more to investigate there!

    thx
    Pat

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Linked Table Updates (Access XP)

    Have you resolved your issue with SQL tables? If not, can you provide more details on how the tables are being viewed - at the table level or on a form for example, and if on a form, some details about what is not being properly displayed?

    We regularly use SQL tables linked via ODBC, and in all but the most esoteric cases do not need to do any refresh to see current data.
    Wendell

  10. #10
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Table Updates (Access XP)

    To summarise the structure of my database I have around 20 tables in SQL with an ODBC connection to the Access front end. There is only the 1 table that is causing me problems, a sample of the table as displayed in Access is shown below.

    AssetNo UpdateDate TRFrom TRTo TRReason ACApprovalDate UpdateDescription Cost EventID UpdateID
    51867 16/09/2003 Someone Somewhere Because

Posting Permissions

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