Update SQL Database table specific column value using Excel VBA code on the basis of condition RRS feed

  • Question

  • Hi,

    I am very new in VBA code. If some one can help me it will be really appreciated

    I want update "Upload" column on Button click on the basis of ID, some time i want update on record some time multiple record at one click.



    Sarvajeet kaur

    Thursday, January 9, 2020 5:42 PM

All replies

  • Hi, Here is the basic update code needed to update a single record. I will leave it to you to cycle through your list and call this subroutine. Be sure you create the following 'references' in your workbook (Developer / Visual Basic / Tools / References) - Microsoft ActiveX Data Objects 2.8 Library / Microsoft ActiveX Data Objects Recordset 2.8 Library

    This code just runs a normal SQL 'Update' command.  You need to change some of the parameters inside the code.

    Public Sub Update_Status_in_SQL(Update_To As String, ID_Key As Integer)

    On Error GoTo Update_Status_in_SQL_Error

    'Initializes variables
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim ConnectionString As String
    Dim StrQuery As String

    Dim SQL_Table_to_Update As String

        ' Set the name of the SQL table to be updated
        SQL_Table_to_Update = ""

        ' You need to configure your connection string.  The following works for SQL Server 16.  Replace 'Server' and 'Database' parameters to your configuration.
        ConnectionString = "Driver={SQL Server Native Client 11.0};Server=BI16PROD;Database=BusinessIntelligence;Trusted_Connection=yes"
        Application.Cursor = xlWait
        'Opens connection to the database
        cnn.Open ConnectionString
        'Timeout error in seconds for executing the entire query; this will run for 5 minutes before VBA timesout
        cnn.CommandTimeout = 300
        Set rst = New ADODB.Recordset
        Set rst.ActiveConnection = cnn
        ' Construct the 'Update' query for the two input values
        StrQuery = "UPDATE " & SQL_Table_to_Update & " SET [Upload] = " & Update_To & " WHERE [ID] = " & Str(ID_Key)
        ' Run the update query
        rst.Open StrQuery

        Application.Cursor = xlDefault
        Set rst = Nothing
        Set cnn = Nothing

    Exit Sub


        Application.Cursor = xlDefault
        Set rst = Nothing
        Set cnn = Nothing
        MsgBox ("Error in Update_Status_in_SQL:  " & "Update_To = " & Update_To & " - for ID_Key = " & Str(ID_Key) & vbCrLf & "Err #" & Err.Number & " - " & Err.Description)

    End Sub

    Friday, January 31, 2020 8:11 PM