none
Updating SQL Records in Excel RRS feed

  • Question

  • I am honestly running around in circles on this problem, most of the solutions I find online seem to be much more robust then what I need and I am starting to question if it is even doable.

    I have an Excel Workbook with a sheet labeled "ServerVulnerabilities", this sheet is using a Data Connection to a single SQL Server table with the same name. 

    Column A is VulnerabilityID (the table primary key), Columns B-E are "ManagerAssigned", "StaffAssigned", "Status" and "Comments". The other columns, there are a lot, are unimportant for my question. 

    I want to know if it is possible to make a VBA macro that anytime a user updates one of the values in columns B-E it will update that same column name in SQL where the VulnerabilityID = the value in the A column on the same row. 

    Any help would be greatly appreciated. 

    Thursday, December 31, 2015 3:05 PM

Answers

All replies

  • Hi Stanton,

    For this requirement, I suggest that you could capture data change by using Worksheet.Change event, then get the corresponding VulnerabilityID value, after that using ADO to update the records in SQL Server.

    # Using ADO with SQL Server Native Client

    https://msdn.microsoft.com/en-us/library/ms130978.aspx?f=255&MSPPError=-2147217396

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 1, 2016 6:07 AM
    Moderator
  • Thank you very much, I will check it out! 
    Sunday, January 3, 2016 8:56 PM
  • Hi Stanton,

    Please feel free to post the issue here or open the new threads if you have the issue.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 4, 2016 2:10 AM
    Moderator