Hello,
I'm an Access beginner, so forgive me if this is a simple question.

I have a form that generates a SQL statement based on user input. the SQL statement is constructed as follows:
 sql = "update song set composer = '" & composer & "', alternate_title = '" & _
        alternate_title & "', key = '" & key & "' where title = '" & title & "'"

on inspection, the string is
"update song set composer = 'update', alternate_title = 'update', key = 'C major' where title = 'new song'"

if I put this in a query window, it works just fine, but when I try to update it through a RecordSet like this
rsAddSong.Open sql, localConnection, adOpenDynamic, adLockOptimistic, adCmdText
I get an error 'Syntax error in UPDATE statement'. I have the same problem with an insert statement that reads
insert into song (title, composer, alternate_title, key) values ('new song 2','c','d','A flat major')

I do have a primary key on the table (song_id) that I'm not specifying, since it's an AutoNumber field. is that the problem?

thanks!
Jo

Hi Jo,

The problem is that insert, update and delete statements in SQL (known as "action queries" in Access) don't return Recordsets. They simply perform their respective actions on tables. To execute these statements using ADO you don't open a recodset; instead, you execute a command. The procedure below gives you a rough idea how, though its untested. Call the procedure from somewhere in your code passing in your update values (the insert is handled similarly). The procedure creates a parameter object for each of your update values, appends the parameter objects to a command objects parameters collection, and then simply executes the command (a parameterized query named "qrySongUpdate"):

The SQL for qrySongUpdate would look like:

PARAMETERS inComposer Text ( 255 ), inAlternate_Title Text ( 255 ),
           inKey Text ( 255 ), inTitle Text ( 255 );
UPDATE Song SET Composer = inComposer, Alternate_Title = inAlternate_Title,
                Key = inKey
WHERE (((Song.Title)=[inTitle]));


~~~~~~~~~~Code~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~

' Call UpdateSong("update", "update", "C major", "New Song")

Public Sub UpdateSong(strComposer As String, strAlternate_Title As String, _
            strKey As String, strTitle as String )
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim paramComposer As ADODB.Parameter
    Dim paramAlternate_Title As ADODB.Parameter
    Dim paramKey As ADODB.Parameter
    Dim paramTitle As ADODB.Parameter

    Set cnn = CurrentProject.Connection

    Set cmd = New ADODB.Command
    cmd.CommandText = "EXECUTE qrySongUpdate"
    cmd.CommandType = adCmdText

    'Composer Paramter
    Set paramComposer = cmd.CreateParameter("inComposer", adVarChar, adParamInput, 40)
    cmd.Parameters.Append paramComposer
    paramComposer.Value = strComposer

    'Alternate_Title Paramter
    Set paramAlternate_Title = cmd.CreateParameter("inAlternate_Title", adVarChar, adParamInput, 40)
    cmd.Parameters.Append paramAlternate_Title
    paramAlternate_Title.Value = strAlternate_Title

    'Key Paramter
    Set paramKey = cmd.CreateParameter("inKey", adVarChar, adParamInput, 40)
    cmd.Parameters.Append paramKey
    paramKey.Value = strKey

    'Title Paramter
    Set paramTitle = cmd.CreateParameter("inTitle", adVarChar, adParamInput, 40)
    cmd.Parameters.Append paramTitle
    paramTitle.Value = strTitle

    cmd.ActiveConnection = cnn
    cmd.Execute

    cnn.Close
    Set cnn = Nothing
    Set cmd = Nothing
    Exit Sub

ErrorHandler:

    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then cnn.Close
    End If
    Set cnn = Nothing
    Set cmd = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

HTH,

Bob

Also, though not nearly as fun as playing with object models, you could build your SQL string in code as you have, store it in a string variable, then use the string variable as an argument of the DoCmd.RunSQL method. DoCmd.RunSQL is an Access specific method of executing "action queries".

The advantages of using Command objects are:

1. SQL in stored query objects is easier to maintain than in-line SQL scibbled in code modules.

2. Command objects can return information about the action taken: success or failure, number of records affected, output parameters (when used with SQL Server stored procedures). DoCmd.RunSQL can't return any values at all.

3. Treating Access saved query objects as stored procedure-like objects provides a consistant programming model when moving between Access and SQL Server. In fact, I create parameterized stored queries in Access using the following SQL syntax:

CREATE PROCEDURE procProductsAddItem(inProductName VARCHAR(40), inSupplierID LONG, inCategoryID LONG)
AS INSERT INTO Products (ProductName, SupplierID, CategoryID) Values (inProductName, inSupplierID, inCategoryID);

and treat them as much like stored-procedures as Access will allow. The above syntax works in both Access(2K+) and SQL Server.

HTH,

Bob

thank you very much, Bob! the DoCmd works wonders.