I have a split database, and each user has their own FE. A couple of folks in the office would like to pull data from it. Both setups call for pulling from a query I made for them that reside in the BE. I setup their Excel workbook connections for them, and everything works until someone is using the DB itself, then Excel chokes on the connection. I have the db itself setup for multiple users, and it works fine, but Excel seems to want exclusive access. This is a new thing to me, and I've googled around, but can't find information on the subject. Is this even possible with multiple users?
Excel to Access Data Connection Multiple Users Question (1 Viewer)
- Thread starter DeanFran
- Start date
- Local time
- Today, 07:37
- Joined
- Oct 29, 2018
- Messages
- 21,467
Hi. Thanks for the clarification. Unfortunately, since I am not near any computer right now or any time soon, I won’t be able to test anything for you at the moment. Hope Arnel can though. Good luck!
I was able to find something. If you use the "From Other Sources" option in Excel's "Get External Data" tool, you can choose "From Microsoft Query". Once you've connected to the desired tables, you basically roll your own query from inside Excel, including pulling from multiple related tables. The nice part about this, is if you set it to refresh upon opening the Excel file, once the data is refreshed, it appears to close the connection. Also, for whatever reason, it allows others to use the database while the Excel file is open, and if others are already using the database, Excel is allowed to get data from Access. I will do some more testing, but this might be the answer.
- Local time
- Today, 07:37
- Joined
- Oct 29, 2018
- Messages
- 21,467
Hi. That’s good news. Thanks for the update.
