+ Reply to Thread
Results 1 to 3 of 3

Running a SQL Update Query in Excel VBA

  1. #1
    KC
    Guest

    Running a SQL Update Query in Excel VBA

    I am using VBA in Excel to connect to a SQL server and retreve data. That
    is working. The problem is that I want to run an Update query to update one
    line in the database and it isn't working. Below is an example of the code I
    am using.


    Dim cmdCommand As New ADODB.Command
    Dim recSet As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    cmdCommand.ActiveConnection = cn
    strSQLCommand = "UPDATE Materials.ReportName = test Where
    materials.MaterialID = 5"
    cmdCommand.CommandText = strSQLCommand
    cmdCommand.CommandType = adCmdText
    Set recSet = cmdCommand.Execute
    please help
    --
    KC

  2. #2
    Robert Bruce
    Guest

    Re: Running a SQL Update Query in Excel VBA

    Roedd <<KC>> wedi ysgrifennu:

    > I am using VBA in Excel to connect to a SQL server and retreve
    > data. That is working. The problem is that I want to run an Update
    > query to update one line in the database and it isn't working. Below
    > is an example of the code I am using.
    >
    > Dim cmdCommand As New ADODB.Command
    > Dim recSet As New ADODB.Recordset
    > Dim cn As New ADODB.Connection
    > cmdCommand.ActiveConnection = cn
    > strSQLCommand = "UPDATE Materials.ReportName = test Where
    > materials.MaterialID = 5"
    > cmdCommand.CommandText = strSQLCommand
    > cmdCommand.CommandType = adCmdText
    > Set recSet = cmdCommand.Execute
    > please help


    Your UPDATE syntax is wrong. The correct syntax for update is:

    UPDATE table_name
    SET column_name = new_value
    WHERE column_name = some_value

    so try something like "UPDATE Materials set ReportName = 'test' Where
    MaterialID = 5". Note that I have also enclosed the string value 'test' in
    single quotes.

    HTH

    Rob




  3. #3
    RB Smissaert
    Guest

    Re: Running a SQL Update Query in Excel VBA

    Try something like this:

    Sub test()

    Dim objCommand As ADODB.Command
    Set objCommand = New ADODB.Command

    With objCommand
    .ActiveConnection = ADOConn
    .CommandText = "UPDATE Materials " & _
    "set ReportName = 'test' " & _
    "Where MaterialID = 5"
    .Execute
    End With

    End Sub


    RBS

    "KC" <KC@discussions.microsoft.com> wrote in message
    news:0DBBD930-BD1B-44C7-AB0A-6A0FB6719D36@microsoft.com...
    > I am using VBA in Excel to connect to a SQL server and retreve data.
    > That
    > is working. The problem is that I want to run an Update query to update
    > one
    > line in the database and it isn't working. Below is an example of the
    > code I
    > am using.
    >
    >
    > Dim cmdCommand As New ADODB.Command
    > Dim recSet As New ADODB.Recordset
    > Dim cn As New ADODB.Connection
    > cmdCommand.ActiveConnection = cn
    > strSQLCommand = "UPDATE Materials.ReportName = test Where
    > materials.MaterialID = 5"
    > cmdCommand.CommandText = strSQLCommand
    > cmdCommand.CommandType = adCmdText
    > Set recSet = cmdCommand.Execute
    > please help
    > --
    > KC



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts