Excel to SQL remotely update to specific users

Question

I have an SQL table on a server and I have imported it into an Excel spreadsheet, when I send this file to someone else who also has access to that server they are able to see the data but unable to refresh the data. It gives file not available error. Please let me know how can I enable those users also to refresh data at their convenience.

Second part is if any of the above users make changes in the Excel spreadsheet, can we update the SQL table dynamically? P.S there are 19 tables and each table has 20 columns so OPENROW is not a viable option for me. Any help or pointers are highly appreciated.

Answer 1

use savetodb plugin for excel

http://www.savetodb.com/

create buttons in excel to load db and save changes to db

Dim addIn As COMAddIn
Dim addInObj As Object
Set addIn = Application.COMAddIns("SaveToDB")
Set addInObj = addIn.Object
addInObj.Save

I have used it multiple times, for multiple people. a word of caution, anytime you give access like this to a table in a db, you give people the ability to muck tables and dbs up. have a good backup plan handy.

you(or db admin) will need to give write access to users who will be updating these tables to the db.