Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 17th, 2005, 05:13 PM
Registered User
Join Date: Jan 2005
Location: , , .
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?

Reply With Quote
  #2 (permalink)  
Old January 17th, 2005, 07:47 PM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
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



Reply With Quote
  #3 (permalink)  
Old January 17th, 2005, 10:27 PM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
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.



Reply With Quote
  #4 (permalink)  
Old January 18th, 2005, 06:05 PM
Registered User
Join Date: Jan 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts

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

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 07:54 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.