Results 1 to 4 of 4
  1. #1
    csako1 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    3

    Question UPDATE and INSERT


    i'm creating a ms access application where in update records if ID is already existing otherwise insert into table if not..

    i've use IF EXISTS but i got error from my query. it says that expected UPDATE,SELECT, INSERT, DELETE into expression..

    how to do this in single query??? looking forward on response.

    thank you very much and have a great day ahead. Godbless

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Simply put, you can't, at least to my knowledge. You first have to check to see if the ID exists, THEN run the according UPDATE or INSERT query. 2 queries.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't think you can do it all in one query. But if you had a small vba routine along these lines

    Sub YourRoutine()
    Dim SqlUpdate as String 'you have to create the sql for Update
    Dim SqlInsert as String 'you have to create the sql for Insert
    Dim SQL as string
    Dim db as dao.database
    set db = currentdb

    If DCount("ID","YourTable", criteria you are using) >0 Then
    'update code here
    db.execute sqlUpdate , dbfailonerror
    else
    'Insert code here
    db.execute sqlInsert , dbfailonerror
    end if

    see http://www.techonthenet.com/access/f...ain/dcount.php
    for DCount


    oops I see theShabz responded while I was typing.
    Last edited by orange; 01-06-2012 at 07:39 AM.

  4. #4
    csako1 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    3
    Quote Originally Posted by orange View Post
    I don't think you can do it all in one query. But if you had a small vba routine along these lines

    Sub YourRoutine()
    Dim SqlUpdate as String 'you have to create the sql for Update
    Dim SqlInsert as String 'you have to create the sql for Insert
    Dim SQL as string
    Dim db as dao.database
    set db = currentdb

    If DCount("ID","YourTable", criteria you are using) >0 Then
    'update code here
    db.execute sqlUpdate , dbfailonerror
    else
    'Insert code here
    db.execute sqlInsert , dbfailonerror
    end if

    see http://www.techonthenet.com/access/f...ain/dcount.php
    for DCount


    oops I see theSshabz responded while I was typing.
    i'm going to try this one. but can you teach me how to put a sub routine in ms access??? i'm a newbie in this kind of development in ms access.. thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. if exists UPDATE else INSERT
    By lloyddobler in forum Programming
    Replies: 18
    Last Post: 01-04-2012, 11:35 PM
  2. SQL Insert / Update Merged (Video)
    By robsworld78 in forum Access
    Replies: 4
    Last Post: 12-21-2011, 07:36 PM
  3. Waiting for Insert/Update to finish
    By EddieN1 in forum Access
    Replies: 6
    Last Post: 12-12-2011, 12:15 PM
  4. Insert/update value of Access Table via UDP
    By Yance in forum Programming
    Replies: 1
    Last Post: 03-21-2011, 06:57 PM
  5. update vs. delete / insert
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 09-23-2010, 07:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums