Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 17th, 2005, 05:13 PM
Registered User
Join Date: Jan 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default update/insert work in queries but not on form

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?

Old January 17th, 2005, 07:47 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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

    Set cnn = Nothing
    Set cmd = Nothing
    Exit Sub


    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



Old January 17th, 2005, 10:27 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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.



Old January 18th, 2005, 06:05 PM
Registered User
Join Date: Jan 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
IIF Formulas used in Update queries kypros79 Access 5 April 12th, 2018 09:06 PM
Insert won't work Tod1 Access VBA 1 December 18th, 2006 10:50 PM
Database Insert/Update problem with VB.NET form tino mclaren VB Databases Basics 2 February 4th, 2005 05:12 AM
Update queries socoolbrewster Access 4 March 4th, 2004 06:18 PM
Creating INSERT queries dynamically at runtime wpsprogrammer ADO.NET 3 January 26th, 2004 10:38 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.