Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Insert into' sql string (A2k2)

    Hello All,
    I have an append SQL string that executes when I open a form. It works the first time I run it but then gives an error when I execute it a second time. The error is 3022 which basically says that if the code runs, it will create a duplication of an index or key. I understand why it is happening. My question is: How do I make the append just ignore duplicates? For example, if I have three records and two of them already exist in the destination table, I want those two ignored but the remaining record to be added.

    Here is what I am doing:

    strSql = "INSERT INTO Parts_WO ( part_so_number, part_line_item, Part_Number, Part_Description, Part_Qty, part_rma ) " _
    & "SELECT DISTINCTROW SODetail1.ORDNUM_28, SODetail1.LINNUM_28, SODetail1.PRTNUM_28, " _
    & "[Part Master].PMDES1_01, SODetail1.SHPQTY_28, SODetail1.UDFREF_28 " _
    & "FROM SODetail1 INNER JOIN [Part Master] ON SODetail1.PRTNUM_28 = [Part Master].PRTNUM_01 " _
    & "WHERE (((SODetail1.ORDNUM_28)= '" & strSO & "')) AND ((SODetail1.STK_28) Not Like ""100"");"

    CurrentDb.Execute strSql, 128

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Insert into' sql string (A2k2)

    I hope this works. If not, provide more details about the primary key of Parts_WO.

    - Add Parts_WO to the query.
    - Join it to the appropriate table (probably SO_DETAIL1) on the appropriate field(s).
    - Double click the join and specify that you want to return all records from the other table.
    - Add a field from the primary key of the table you added, and set the criteria line to Is Null. Make sure that Append To for this field stays blank.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Insert into' sql string (A2k2)

    Thanks Hans,
    That appears to have worked.

    Thanks,
    Mark

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Insert into' sql string (A2k2)

    Hello All,
    I tried what Hans suggested and it worked. This takes care of inserting records that aren't there. Is there anyway to delete records that don't match? What I am doing now: Opening form, run append query (form ODBC table to access table), insert records that match into access table, end.

    What I also want to do: Open form, check to make sure entries in access table correlate with ODBC table entries, if entry exists in access table but not ODBC, delete access table record.

    Has anyone had experience with this sort of thing before?

    Thanks,
    Mark

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Insert into' sql string (A2k2)

    Create a query based on the Access table (Parts_WO) and the linked table (SODetail1).
    Join them on the appropriate field, then double click the join and specify that you want to include all records from Parts_WO (this is the reverse of what you did for the append query).
    Add * from Parts_WO, and the link field from SODetail1. Clear the Show: check box for this field, and set the Criteria: to Is Null.
    At this point, you should have a select query that returns all records in Parts_WO that have no match in SODetail1. You could also have created it with the Find Unmatched Query Wizard.
    Now, turn it into a Delete query (Query menu.) The Delete: option for Parts_WO.* will be "From" and for the link field, it will be "Where".
    Save this query, and execute it from the On Open or On Load event of the form. Or note its SQL and execute that.

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

    Re: 'Insert into' sql string (A2k2)

    In A2k, the query needs an explicit DISTINCTROW keyword or to have Unique Records set to yes in its property sheet. Otherwise, it will announce that it is not updateable.
    Charlotte

Posting Permissions

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