Remote upgrade of Excel to SQL for specific users

Question

I have an SQL table on the server, and I imported it into an Excel spreadsheet, when I send this file to someone else who also has access to this server, they can see the data but cannot update the data. This gives the file an inaccessible error. Please let me know how I can allow these users to update the data as they wish.

The second part - if any of the above users makes changes to the Excel spreadsheet, is it possible to dynamically update the SQL table? PS there are 19 tables, and each table has 20 columns, so OPENROW not a viable option for me. Any help or pointers are greatly 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 several times, for several people. a word of caution, at any time when you provide access, like this, to a table in db, you give people the opportunity to muck tables and dbs. Have a good backup plan.

you (or db admin) need to provide write access to users who will update these tables to db.

Answer 2

If you try to update records in Excel Server-based SQL Server, you will be disappointed. You want to create an ADP project or an MDB application using MS Access. See Here: Draft Access and Benefits Data Project (MS Access Interface with SQL Server Support)

Answer 3

You can write a plugin / addin for Excel that listens for events triggered to start / end cell editing. You will need to keep track of which tab the user is on, which cell they are editing, and then send SQL updates through the plugin application.

I no longer have access to the source code where I did this, but if this is what you want to continue, and you need help on startup, let me know and I will find for you some links with specifics about the events of the cell.

Currently, all I have is an MSDN link for Office add-ons

Update

I did a quick search and found these links to get you started:

• Create your first Excel application
• Selection Changed event on worksheet
• Cell Edit Event

After you have a plugin that you can track the cell edit event, you just need to write the logic to find out what needs to be updated in the expanded sheet.

It should be noted: each user who edits this spreadsheet and updates the database will have to install your plug-in for this solution or really any solution for work. In Excel, there is no automatic way to do this that I know of.

Answer 4

For problem 1: Does your Excel spreadsheet have macros? (I would ask in the comments, but my representative is too low)

If so, you need to save it as an "Excel Macro-Enabled Workbook" type to include the macros you wrote.