This is a discussion on Why do linked Excel sheets need to be opened for update? within the Microsoft Access forums, part of the Question Forums category; Hello, There is something that I don't understand. To update linked Excel Sheet I need to open the file. Is ...
There is something that I don't understand. To update linked Excel Sheet I need to open the file. Is there any reason? Why is Access not able to do this?
As for Excel, it requires all the information to be present and available before it will update the sheet. Updating is manual, hence operator has to cause the sheet to update once data is put in, to a degree. By not opening a linked sheet, the current sheet does not find the "linked" information thus dead ends there. In excel the operator has to manually save any new data at some point. Even with auto save, at the end of the session and the sheet is closed, Excel will ask you if you want to re-save the newly input data. With a yes it is saved, with a no it's is not saved and retains the status when first opened. I suspect one could write a macro that causes the linked sheet to automatically open, update, then close itself.
Access is actually updating everytime you add a new record, automatically. This is referred to as "real time." Hence, as one creates a new record, the new record is being save upon moving to the next record. When you close out of an Access session you don't have to "re-save" the info. This is unless you make a programing change that affects the data/records.
Plus, with Access you can create as many tables as you need, link them together with in the same file, thus never having to go outside the current file.
Excel will store only 64,000 records, where Access is unlimited. Access allows records to be stored in a lot tighter, more secure structure, i.e., less likely to get a date in one record mixup in another record. This is referred to as "data integrity.
Hope that helps, know it's confusing, but this is **** interesting stuff.