Database (Desktop - Sharing Folder) linking - simultaneous users

[paulcherianc]

I am trying to keep the master source file on my desktop which will be linked to a workbook kept on the sharing folder on a network drive. The shared file will be again linked to multiple concurrent users. So, whenever I am updating on my master database, it will be updating the records on other users workbooks as a linked table. * Is there any issue with the data flow when multiple users access the same linked workbook on the sharing folder?
How can I do this process? Which will be the best medium to do this?

Can any Ninjas advice me in this regards. For better clarity, I have attached a pictorial representation.

 

[Chihiro]

Depends on what sort of data manipulation is done, if any. And if end users write back to shared/database.

If end users write back to shared/database... Excel isn't your best choice. While it is entirely possible to do, there are many obstacles that you need to overcome, and requires frequent manual maintenance.

I'd recommend investing in true database (MySQL, SQL Server, Postgre etc), or some other tool.

 

[paulcherianc]

Super admin only will have the access rights to edit the Original workbook on his desktop and the database on the sharing folder. Users will not manipulate any data on these two. Thus, end users are not allowed to write back to shared/database.

This is just for the users to select some fields from a list. I have given a sample workbook. Where you have to consider each sheet as a workbook.

 

[Chihiro]

In that case, depending on Excel version there are many options.

My choice would be...

1. VBA - All versions, leveraging ADO (search the forum for ADO or ADODB and you should find plenty of examples).

2. PowerQuery/Get & Transform - If using Office 365, Excel 2016, 2010. If using 2013, need specific license SKU.

3. MS Query - All versions