Results 1 to 8 of 8

Thread: if not exists insert.. to access[RSLVD]

  1. #1

    Thread Starter
    Fanatic Member kevin_sauerwald's Avatar
    Join Date
    Feb 2002
    Location
    outside Philly
    Posts
    516

    Resolved if not exists insert.. to access[RSLVD]

    I just want this code to work but I think my syntax is off for doing the sql... Im kinda used to using sybase sql... how do you do an if exists and begin end insert/update ??

    here what I had.. it didnt like it

    MySql = ""
    MySql = "IF NOT EXISTS ( SELECT 1 FROM users WHERE user_id = '" & PC_name & "')"
    MySql = MySql + " BEGIN "
    MySql = MySql + "INSERT users (user_id,last_name,first_name,location) "
    MySql = MySql + "VALUES (" & "'" & PC_name & "','last','first','loc1') "
    MySql = MySql + "END"
    Debug.Print MySql
    'open the workout table
    rs.Source = MySql
    rs.Open
    Last edited by kevin_sauerwald; Oct 1st, 2004 at 09:10 AM.

  2. #2
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439
    In Access?

    AFAIK you would have to use a recordset to check for the EXISTS part and execute an INSERT INTO statement to do your insert. There may be another way, but if there is I dont know it.

    Shout if you need more help.

  3. #3

    Thread Starter
    Fanatic Member kevin_sauerwald's Avatar
    Join Date
    Feb 2002
    Location
    outside Philly
    Posts
    516
    ok so lets say I do the if exists first... how do I check if
    it got something back... yes this is to ACCESS...
    and will it like the if exists like the way I have it ?
    or do I just make a select from and see if it returned anything ?
    and once again.. is there a qucker way to just run some sql and
    check if it returned rows or not w/o checking contents ?


    I'm not very familiar with all the ado calls...

    thanks

  4. #4
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439
    Jet SQL has very little functionality Im afraid so you have to use VBA to do all the useful stuff that you can do in MySQl/Transact etc.

    So you need to open a recordset and check to see if it is empty first. Then execute your INSERT if it returns nothing. Something like

    Code:
    Dim rs as ADODB.Recordset
    Dim strSQL as String
    
    
    Set rs = new ADODB.Recordset
    
    rs.open "SELECT * FROM MyTable " & _
                 "WHERE MyField = MyValue", MyConnection, dOpenForwardOnly, adLockReadOnly
    
    If rs.EOF then
    
       strSQL = "INSERT INTO MyTable (Field1, Field2.........) " & _
                     "VALUES (Val1, Val2..........)"
    
        MyConnection.Execute strSQL
    
    End If
    Hope that helps

  5. #5

    Thread Starter
    Fanatic Member kevin_sauerwald's Avatar
    Join Date
    Feb 2002
    Location
    outside Philly
    Posts
    516
    yep.. thanks...

  6. #6

    Thread Starter
    Fanatic Member kevin_sauerwald's Avatar
    Join Date
    Feb 2002
    Location
    outside Philly
    Posts
    516
    keeps on telling me there is something wrong with my FROM clause

    MySql = ""
    MySql = "SELECT last_name FROM user WHERE user_id = '" & PC_name & "'"
    Debug.Print MySql
    'open the workout table
    rs.Source = MySql
    rs.Open


    debug shows

    SELECT last_name FROM user WHERE user_id = 'DELL750'

    looks good to me ??

  7. #7
    Hyperactive Member Granty's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    439
    User is probably reserved, try [user]

  8. #8

    Thread Starter
    Fanatic Member kevin_sauerwald's Avatar
    Join Date
    Feb 2002
    Location
    outside Philly
    Posts
    516
    bingo.. thanks a bunch.. I never would have
    thought of that one... if I was helping someone
    else with sybase I would have thought of that... duh..

Posting Permissions

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



Click Here to Expand Forum to Full Width