Refresh linked table with VBA code

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?



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
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?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Try to add this to the OnOpen event of the form (requires a reference to Microsoft DAO):


and perhaps a


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

prettykittyqAuthor Commented:
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.


Gustav BrockCIOCommented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.