Excel Data Link to Another Excel Workbook

[mwisnefsky]

Hello,

I have 4 excel workbooks that are basically data entry forms that (through VBA) dump into a common data storage workbook. On each of the data entry workbooks, I have set up a data link to the sheets in the data storage workbook. This lets people with access only to one of the data entry workbooks access to all the data so they can view reports...etc...

However - the data link seems to be locking the data storage workbook as read only the entire time the data entry workbook is open. I did some research and have tried modifying the "Mode" on the data link connection string:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=J:\IE\IESHARE\KPC Trackers\Data Storage\7263989700.xlsx;[COLOR=#ff0000][B]Mode=Read[/B][/COLOR];Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

However, it is still locking the file as read only. Is there a way to get around this? I'd prefer not to have to write some VBA to do this manually, but if I have to I have to.

Does Excel handle data connections between Excel workbooks differently? Would it be better to try and set up an access database to write in to? I chose Excel because I don't know much about Access or writing to databases.

 

[BrianMH]

Would it be better to try and set up an access database to write in to?

Yes it would be much better. Excel is NOT robust in a multiuser environment and you are likely to find the excel files corrupting often. For your own development and sanity I suggest you start learning Access.