How to update an Excel spreadsheet that link to a form while users having the form opened.

[lylyming977]

Hello,

Please let me know what is the best way to accomplish this? I have a database that split into the Front end and the Back end. The form that the users entering the information uses the queries from two sources. One is the a table within Access and the other is an excel spreadsheet that I link to it. How can I update /replace the Excel spreadsheet while users are using the form of the front End?



Thank you in advance.
Lylyming977

[June7]

A form can edit/update only one table. Why is the link included on this form?

[lylyming977]

I used Linked Table to link the Excel spreadsheet. The information on the form is from a query. The query gets the information from the table within the database and the Excel spreadsheet is from another folder.

[June7]

I understand linking. Why is the link included in this form RecordSource?

[lylyming977]

sorry, not sure if I understand your question because I'm not very good at Access. I need to link that excel spreadsheet because it feeds new information that I want to pull into my Access Database. If you have other way to do so, please share.. I appreciate your suggestions.

[June7]

Linking is fine. However, including the link in that form's RecordSource might not be appropriate. A form can enter/edit data to only one table (and Excel cannot be edited from Access). So why do you need data from the link in that form?

[lylyming977]

I want to display that information from the spreadsheet on the form along with the data on the table. However, I run into this problem. If someone has the database opened, I couldn't replace that spreadsheet. That spreadsheet is from other department and the data is constantly changing. I want to load the data into my database so that I don't have to enter it again. I only update the data from the table. So, one employee but has some info from the spreadsheet and some from my database which I can do the update.

[June7]

The reality is that spreadsheet cannot be edited if it is in use by the database. Period. An open form that has the link in its RecordSource locks the spreadsheet. Any open query that includes the link will cause the spreadsheet to lock, even a combobox/listbox RowSource.

However, a domain aggregate expression that references the link does not lock the spreadsheet.

[lylyming977]

So in order to replace the file, I should have exclusive access to the database file. How can I flag the front ends users that the back end is off line so that I can replace the file and it disables the Application (front ends)? And how can I bring the back end off line and disable the application? I will bring it online again when I am done.

[June7]

You don't need to have exclusive use of the database, just no one can have the link in use. Even if you do have exclusive use of the database, if you have that form open then can't edit the spreadsheet.

Don't know how to make a backend 'offline' - except maybe to change the Windows file permissions.