none
Update statement for a SQL using VBA in Excel workbook RRS feed

  • Question

  • I am trying to update a SQL table on its date field using the VBA code. What I want it to do is recognize the OrderNumber that is on the Excel sheet and match it with the corresponding OrderNumber in the database. Then I want it to update the OrderDate field with Today's date. I'm not familiar with VBA so I can provide additional information if it's needed. 

    Sub Button1_Click()
    Dim conn As New ADODB.Connection
      
      
                
            'Open a connection to SQL Server
            conn.Open "Provider=SQLOLEDB;Data Source=ANF-M2MCLIENT;Initial Catalog=ExcelDemo;Integrated Security=SSPI;"
                
            
                    
    
                Const sql As String = "UPDATE Orders SET Orders.OrderDate = 7-17-2019 WHERE Orders.OrderNumber = 033707"
     
                
            MsgBox "Date updated."
                
            conn.Close
            Set conn = Nothing
                 
        
    End Sub

    Another way I tried

    Public Sub SaveOrderDate(ByVal orderNumber As String, ByVal orderDate As Date)
    
        Dim conn As ADODB.Connection
        Set conn = New ADODB.Connection 
        conn.Open "Provider=SQLOLEDB;Data Source=ANF-M2MCLIENT;Initial Catalog=ExcelDemo;Integrated Security=SSPI;"
    
        Const sql As String = UPDATE Orders SET Orders.OrderDate = 7-17-2019 WHERE Orders.OrderNumber = 033707"
    
        Dim cmd As ADODB.Command
        Set cmd.ActiveConnection = conn
        cmd.CommandType = adCmdTypeText
        cmd.CommandText = sql
    
        cmd.Parameters.Append cmd.CreateParameter(Type:=adDate, Value:=orderDate
        cmd.Parameters.Append cmd.CreateParameter(Type:=adVarChar, Value:=orderNumber)
    
        cmd.Execute
        conn.Close
    
    End Sub
    


    • Edited by David9501 Thursday, July 18, 2019 2:25 PM
    Thursday, July 18, 2019 1:41 PM