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