0

I currently have a spreadsheet that contains information that I need to use to update a table in SQL Server 2017. I have stepped through the code and it appears that the connection was successfully made, however the updates are not being made. What I need assistance with is editing my VBA code so that upon the "Yes" button click, the updates to the SQL Server table are successfully made. My code seems to run when I do select "Yes" so I think the only area that needs to be assessed are the test and the connect_to_sql_server calls. Thank you so much.

Sub Button2_Click()
    Dim ans As Variant
    
    ans = MsgBox("Upload?", vbQuestion + vbYesNo, "Upload Student")
    
    If ans = vbYes Then
        Call test
    ElseIf ans = vbNo Then
    End If
End Sub

Sub test()
    Dim Server_Name As String
    Dim DatabaseName As String
    Dim SQL As String
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim test As String
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Calculator")
    
    VID = ws.Cells(3, 2)
    test = "'Ready for Approval'"
    
    Server_Name = "server"
    DatabaseName = "db"
    SQL = "UPDATE table_name SET APP_VERSION_STATUS = " & test & " WHERE APPLICATION_VERSION_ID in (" & VID & ")"
    Debug.Print (SQL)
    
    Call connect_to_sqlserver(Server_Name, DatabaseName, SQL)
    MsgBox ("DONE")
End Sub
    
Sub connect_to_sqlserver(ByVal Server_Name As String, ByVal Database_Name As String, ByVal SQL_Statement As String)
    Dim strConn As String
    Dim wsReport As Worksheet
    Dim col As Integer
    
    strConn = "Provider = SQLOLEDB;" _
            & "Data Source=CONFIDENTIAL;" _
            & "Initial Catalog=CONFIDENTIAL;" _
            & "User ID=CONFIDENTIAL;" _
            & "Password=CONFIDENTIAL;"
    
    'Update made via note from GSerg                
    SQL_Statement = "UPDATE table_name SET APP_VERSION_STATUS = " & test & " WHERE APPLICATION_VERSION_ID in (" & VID & ")"

    Set conn = New ADODB.Connection
    With conn
        .Open ConnectionString:=strConn
        .CursorLocation = adUseClient
    End With
End Sub

The code below accomplishes what I need

    Sub UpdateTable()
    Dim cnn As ADODB.Connection
    Dim uSQL As String
    Dim rngName As Range
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim VID As String
    Set cnn = New Connection
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Calculator")
    VID = ws.Cells(3, 2)
    cnnStr = "Provider=SQLOLEDB; " & _
                "Data Source=CONFIDENTIAL; " & _
                "Initial Catalog=CONFIDENTIAL;" & _
                "User ID=CONFIDENTIAL;" & _
                "Password=CONFIDENTIAL;" & _
                "Trusted_Connection=No"
        'Set rngName = ActiveCell
    cnn.Open cnnStr
    uSQL = "UPDATE table_name SET APP_VERSION_STATUS = 'Ready For Approval' WHERE APPLICATION_VERSION_ID IN (" & VID & ")"
    Debug.Print (uSQL)
    cnn.Execute uSQL
    cnn.Close
    Set cnn = Nothing
    Exit Sub
    End Sub
bgado
  • 41
  • 6
  • You never do anything to your `ByVal SQL_Statement As String`, so naturally nothing happens. Before you start doing anything to it, please see https://stackoverflow.com/q/332365/11683. – GSerg Sep 07 '21 at 16:29
  • Okay so I added in a line for **SQL_Statement** and input the Update statement that I want to execute, however updates still aren't being made. Any ideas? Also, mind sharing what the link is for? – bgado Sep 07 '21 at 16:42
  • 1
    Would be helpful to update the code in your post to show exactly what changes you made. – Tim Williams Sep 07 '21 at 17:28
  • Hi @TimWilliams, I added the update I made and commented where in the code. Thanks for taking a look! – bgado Sep 07 '21 at 18:06
  • See an example of running an update here: https://stackoverflow.com/questions/22229765/update-sql-server-table-from-excel-vba – Tim Williams Sep 07 '21 at 18:16
  • Hi @TimWilliams, thanks for the reference. I retooled my query to fit the basis of the one that you linked and got the result I needed. I'll post the finalized code in my OP, thanks again! – bgado Sep 07 '21 at 18:39
  • 1
    Better to post your working code as an answer, so anyone coming along later will know there's a solution. – Tim Williams Sep 07 '21 at 18:46

1 Answers1

0
    Sub UpdateTable()
    Dim cnn As ADODB.Connection
    Dim uSQL As String
    Dim rngName As Range
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim VID As String
    Set cnn = New Connection
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Calculator")
    VID = ws.Cells(3, 2)
    cnnStr = "Provider=SQLOLEDB; " & _
                "Data Source=CONFIDENTIAL; " & _
                "Initial Catalog=CONFIDENTIAL;" & _
                "User ID=CONFIDENTIAL;" & _
                "Password=CONFIDENTIAL;" & _
                "Trusted_Connection=No"
        'Set rngName = ActiveCell
    cnn.Open cnnStr
    uSQL = "UPDATE table_name SET APP_VERSION_STATUS = 'Ready For Approval' WHERE APPLICATION_VERSION_ID IN (" & VID & ")"
    Debug.Print (uSQL)
    cnn.Execute uSQL
    cnn.Close
    Set cnn = Nothing
    Exit Sub
    End Sub
bgado
  • 41
  • 6