Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    docmd.runsql (Access XP)

    I have written a window in Access that links through ODBC to a SQL database that needs to have the ability to either save or cancel. The form has been designed with this in mind and all the controls do not have a source so that when you hit save there is a docmd.runsql that inserts or edits the entries into the appropriate table. If you click on Close without Save obviously it just closes the window and ignores the changes.

    The script works when the output script is run in Query Analyser but when run in Access VB it comes up with the following message.


    "Syntax error (missing operator) in query expression "Description of Stuff" from updates'.

    The VB Script is:

    strSQL = "update updates " & _
    " set updatedate = " & txtUpdateDate & _
    ", trfrom = '" & TRFrom & _
    "', trto = '" & TRTo & _
    "', trreason = '" & TRReason & _
    "', bmapproval = " & chkBMApproval & _
    ", bmapprovaldate = '" & BMApprovalDate & _
    "', acapproval = " & chkAccountsApproval & _
    ", acapprovaldate = '" & ACApprovalDate & _
    "', updatedescription = '" & txtDescription & _
    "' from updates where updateid = " & txtUpdatesID
    End If
    DoCmd.RunSQL strSQL

    The debug.print sql is

    update updates set updatedate = 16/09/2003,
    trfrom = 'Someone',
    trto = 'Somewhere',
    trreason = 'Because',
    bmapproval = -1, bmapprovaldate = '01/09/2003',
    acapproval = -1, acapprovaldate = '01/09/2003',
    updatedescription = 'Description of stuff'
    from updates where updateid = 10046

    which works fine in query analyser.

    Can anyone tell me what I'm doing wrong because it's now beginning to really annoy me!!!

    Many thanks.

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: docmd.runsql (Access XP)

    DoCmd.RunSQL only handles Access/Jet SQL. It does not recognize the wildcards and date delimiters that T-SQL (SQL Server) use. That's the reason it works in Query Analyzer but you get a syntax error in Access.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Location
    Hildesheim, Niedersachsen, Germany
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: docmd.runsql (Access XP)

    You have enclosed the last two of the date values in single quotation marks, but you haven't done so with the first one.
    Is this the mistake?
    In Access (Jet SQL) date values have to be enclosed in number signs. But I think the single quotes are OK for T-SQL.
    If DoCmd.RunSQL only accepts Jet-SQL-statements, then you have to send your T-SQL-statement to your SQL Server
    in another way. Try using the ADO-command-object.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: docmd.runsql (Access XP)

    What you are doing is creating what is referred to as an "unbound form" in Access circles. However there are a number of traps in doing that sort of thing. For one thing, any sql string done in code must use dates formatted according to "USA standard" mm/dd/yyyy - a real pain in the neck if you are working in another part of the world as you apparently are. In addition, you probably should put a semi-colon ([img]/forums/images/smilies/wink.gif[/img] at the end of the SQL String. Finally, the way we typically do this is to build a query in the Query tool, get it to the point where it works, and then take the SQL String and deconstruct it into code as you have done. Using the SQL Server Query Analyzer can lead you down a twisty path that dead-ends, as others have noted.

    Another alternative we use more often, is to use either DAO - works best on ODBC linked tables - or ADO which uses a native mode connection to SQL, and simply do Updates or Inserts in code without using any SQL commands. Finally, it is possible to create bound forms where you have a choice of updating or not updating when the form is closed or you move to a different record - it involves using the Dirty property of the form and various form events.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: docmd.runsql (Access XP)

    Thanks for all the help. I tried what everyone was suggesting and eventually I copied the output code to the query tool in Access and found that instead of having :

    From updates where updateid = 12345

    as the last part of the code as you would in T-SQL you ignore that and just put:

    where updates.updateid = 12345

    Once again thanks for the help

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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