SQL data connection in Excel - update SQL tables using table in Excel

[andycorson]

I created a SQL query that gathers information for users to determine which items in our system need to be inactivated. The user receives an Excel workbook that has a table with a data connection to the SQL Server database that pulls in the information they need (via stored proc). I want to include a column in the Excel table with a checkbox that users can check in order to indicate that the item should be flagged inactive in the system. I would like for the user to be able to click a submit button to send back TRUE to a table in the database when they check the box. Then I will use this table to generate a report showing all items that need to be inactivated instead having all users save the workbook, check the box, send back to me, and then compile a report by hand. From what I have read, this seems to be possible using an ADO connection, but I haven't been able to figure out how to do this. Does anyone have an idea about this?

Also, I would like to know if it is possible to reference a table within the workbook in the data connection command text box as part of a SQL statement. For example, it seems like I could say "UPDATE tablex x SET x.col2 = [excel_table_1].[col2] WHERE x.col1 = [excel_table_1].[col1]"

Most of the examples I have seen using openrowset require that you include a path to the Excel workbook, but I don't want to do that because we store the Excel files in an eRoom (similar to Sharepoint document library) and I am not sure how to obtain the path to the eRoom. That is why I just want to be able to reference the Excel table within the workbook's command text box, not in a stored proc on the SQL Server side.