Solved

Refresh linked table with VBA code

Posted on 2004-10-31
11,853 Views
Last Modified: 2008-09-15
Kitty's Great Calendar Project gets its data from an Excel table the user generates outside of Access (ExcelCaledar).

Right now, I have the table ExcelCalendar linked to the CalendarProject database but it appears that Access doesn't automatically refresh the data when the CalendarProject database link opens.

I thought that links were supposed to reflect changes in the underlying data.  In fact, I understood that to be the major advantage of using a link instead of just importing the data to a table.

My users have good hearts but tiny brains so I can't expect them to use the Link Manager to refresh the data before proceeding.

How can I use VBA code to refresh the link automatically? (200 points)

And for the other 100 points, IS there an advantage to using the link rather than just importing the data each time I need it?

Thanks.

Kitty

0
Question by:prettykittyq
    6 Comments
     
    LVL 4

    Assisted Solution

    by:dan_vella
    It is strange. I am playing around with this and my linked tables seem to be updating fine.

    Even when the excel table is open it updates straight into access and the access table is refreshed when it is reopened.

    Can you give me any more info?
    0
     
    LVL 18

    Accepted Solution

    by:
    You shouldn't need to refresh the link.

    The concept behind a linked table is just that...a link is created between the Access database and the source.  When the source changes, those changes would be reflects within the mdb.  The source and the MDB are both looking at the same file.  It's not as if the MDB was making a copy and then linking to it.  When working with linked tables, they behave just as if they were a local table in that you can run queries, use them forms/reports.

    Does this help?
    Mike

    0
     
    LVL 48

    Assisted Solution

    by:Gustav Brock
    Try to add this to the OnOpen event of the form (requires a reference to Microsoft DAO):

      DBEngine(0)(0).TableDefs("xlsCalendar").RefreshLink  

    and perhaps a

      Me.Requery

    /gustav
    0
     
    LVL 77

    Assisted Solution

    by:peter57r
    Hello prettykittyq,

    There is something else going on here.
    When you open your database and look at the contents of linked tables there is only the latest data to look at.  There can be no possibility of Access looking at or retaining a previous version of the linked data - that data no longer exists.
    I suggest you look at the table links using the linked tables manager and check that you are linked to what you think you are linked to.

    Pete
    0
     
    LVL 2

    Author Comment

    by:prettykittyq
    I can't test it again because I'm at home and the version of the database that was causing the trouble is at work (on a secure server I can't access from home).  BUT...

    I do know that the ExcelCalendar table didn't refresh last time we used the database.  It was giving us old information.

    One problem might be that we have to delete and replace the Excel spreadsheet every time we want to create a new calendar.  The data is exporting appointment data from an Outlook Calendar to an Excel spreadsheet.  We retain the identical path and Excel spreadsheet name and overwrite the last version of the spreadsheet each time we export the Outlook data.

    I asked in a separate question if replacing the spreadsheet is going to cause us problems if we have a link to that spreadsheet's file name and was told that Access should just find whatever spreadsheet currently resides at the specified location (e.g. c:\\KittyCalendar\\ExcelCalendar.xls) and use that as the linked table.

    It's apparently not doing that.  I went in and ran the Linked Table Manager and that didn't help either.

    I'll play with it again at work on Monday and get back to y'all.  Thanks for the input thus far.

    Kitty

    0
     
    LVL 48

    Expert Comment

    by:Gustav Brock
    It has to work. Access - when you open it - has no other place to draw the data than from the linked workbook. Having done that, it caches the data and if the file is overwritten it has no way to get that information. In that case - if the RefreshLink method will not reveal the new data - you may be forced to delete the link and recreate it (no big deal). This, however, can of course only be done if your Access app don't have the table open.

    /gustav
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    860 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now