Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Refresh linked table with VBA code

Posted on 2004-10-31
6
Medium Priority
?
12,035 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
Comment
Question by:prettykittyq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Assisted Solution

by:dan_vella
dan_vella earned 300 total points
ID: 12456813
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:
Data-Man earned 300 total points
ID: 12456828
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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 300 total points
ID: 12457018
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 300 total points
ID: 12457998
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 1

Author Comment

by:prettykittyq
ID: 12458556
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 52

Expert Comment

by:Gustav Brock
ID: 12458634
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

604 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