Tables linked to Excel

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
I have 9 linked tables to excel spreadsheets that are in a network folder. Is there a macro I can use to automatically update the links once I open the Access database? I hope this is clear enough, I'm quite new to Access.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I believe that they should link themselves automatically.
 
Upvote 0
I see. The excel spreadsheets are constantly updated all day, and I need to run "real time" reports off them. I have a join query to combine them into one table, so then if I update the join query all the tables that are linked will have the most up to date data?
 
Upvote 0
Someone will correct me if I am wrong, but each time a query or table is run (whether it is called directly, or called from Report, Form, or Code), it "refreshes" the data. So it should be up-to-date at the moment in time that request is initiated.

You should be able to test it out to see if it works. I am not sure if the Excel table needs to be saved first for those changes to take effect, though, but that should be easy enough to test also.
 
Upvote 0
jmiskey, thanks for all your insight. One last question. The excel files that are linked are used all day. when I go to run the query, others cannot access those excel files that are linked. is there an option that would allow this?
 
Upvote 0
The excel files that are linked are used all day. when I go to run the query, others cannot access those excel files that are linked. is there an option that would allow this?

Interesting. I wasn't aware of that behavior. Maybe that is to avoid conflicts. If you run the query and then exit, then should be able to get back into those tables again, right? So then they should only be locked out for a few moments.
 
Upvote 0
That is true, once I close the query, the files are accessible. However, I'm setting up reporting applications that senior management can go into and view based on pivot tables and such. If they are in these reports for long periods of time, that will lock out the source excel files that people need to access. I've already received 4 calls today stating they couldn't get in the files, which is what I'd like to work around. Nothing's ever just easy eh?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top