MS Access VBA to Update Linked Tables

Thank you for looking at my question,

I am putting a form together in an Access 2013 database that will enable user(s) to open report(s) via button click.

The tables/queries behind the reports are linked tables from Excel spreadsheets.

I would like the button click to refresh the relevant linked table(s) before opening the report to ensure that the data in the report is current.

How do I do this please using VBA?
Gary CroxfordOperations Support AnalystAsked:
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.

Dale FyeCommented:
use the refreshlink method.

currentdb.tabledefs("linkedExcelTableName").RefreshLink

replace the quoted table name with the name of the Excel table as it is displayed in Access.
0

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
PatHartmanCommented:
I have never had to do this.  When you open the linked spreadsheet, Access retrieves the data so it is already "fresh".  Are you talking about making the spreadsheet run its calculations?  In that case, you would need to automate Excel.  So you would have Access use VBA to open excel and run the function to recalculate the spreadsheet.  Sorry, I don't have code handy.  Since I work with Access rather than Excel, when I need to do some Excel automation, I open Excel and turn on the macro recorder.  Then I try to perform the task I need to automate.  I then copy the code generated by the macro recorder and fix up the references so it will run in Access.
0
Gary CroxfordOperations Support AnalystAuthor Commented:
Thank You Dale
0
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.