I have an MS Access database (.accdb) that I have created with several queries, forms, and macros. The database was created using Linked Tables that point to Excel spreadsheet files on a network drive. We are looking to add a survey capability to this database that would need to update one of the linked tables.
Looking online, it appears that splitting the database between Backend (BE) and Frontend (FE) is the best way to ensure multiple users can access/edit the information without the Excel files being locked out.
However, when I follow the directions on how to split the database, by BE file doesn't have any tables in it. I can manually go in and recreate the links to the Excel files in the BE file, but when I go into the FE file I am not able to link to the BE files.
My Question: Is there a way to have a BE file hold Linked Excel tables, and have the FE file link to the BE linked tables?
No, this is not possible. You can only link directly to tables, you can't link to linked tables.
Also, Excel is not fit for data storage in a multi-user environment unless it's read-only.
You can, of course, link to the Excel files in your front-end, or query the Excel files in your front-end without linking.