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've 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
    • Moved by KareninstructorMVP Thursday, July 18, 2019 2:26 PM Moved from vb.net forum
    Thursday, July 18, 2019 2:16 PM

All replies

  • Hello,

    This forum is for VB.NET, not VBA. I'm moving your question to the Excel for developer forum for better visibility.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, July 18, 2019 2:26 PM