Executing a SQL Statement Containing Parameters

 
Public Sub UpdateWithSQL()
        Dim cmd As New ADODB.Command
        Dim conn As ADODB.Connection
        Dim prm As ADODB.Parameter
        Dim strConn As String
        Dim strSQL As String

        strConn = "Provider=SQLOLEDB.1;" & _
            "Data Source=(local); Initial Catalog=NorthWind;" & _
            "Integrated Security=SSPI"

        Set conn = New ADODB.Connection
        conn.Open strConn
        Set cmd = New ADODB.Command
        cmd.CommandText = "UPDATE Products " & _
            "SET OrderDate = OrderDate, " & _
            "ShipVia = ShipVia, " & _
            "Freight = Freight " & _
            "WHERE OrderID = OrderID"
    cmd.CommandType = adCmdText

    cmd.ActiveConnection = conn

    Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("OrderID").Value = 1

    Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("OrderDate").Value = "10/10/2007"

    Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("ShipVia").Value = 2

    Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("Freight").Value = "1.5"

    cmd.Execute
    conn.Close
End Sub

Related examples in the same category

1.	Creat a SQL statement and append parameter as ceriteria
2.	Creating a Parameter Query
3.	Build the SQL statement dynamically
4.	Running Parameter Queries
5.	User InputBox to read SQL statement parameter

Executing a SQL Statement Containing Parameters : SQL Parameter « Access « VBA / Excel / Access / Word

Related examples in the same category