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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,742
Office Version
  1. 365
Platform
  1. Windows
I believe that they should link themselves automatically.
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,742
Office Version
  1. 365
Platform
  1. Windows
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.
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491

ADVERTISEMENT

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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,742
Office Version
  1. 365
Platform
  1. Windows
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.
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
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?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,490
Messages
5,764,667
Members
425,228
Latest member
Rashid mahmood

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
Top