Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Unanswered: VBA insert into statement ignoring Primary key violations

    I have the following code to insert records into a table :-

    Code:
    Public Function TestLoop()
    On Error GoTo TestLoop_Err
    Dim rsLoop As Recordset
    Dim strSQL As String
    Dim db As Database
     
    Set db = CurrentDb()
    Set rsLoop = db.OpenRecordset("Select * from tblADepAccountID;")
     
    rsLoop.MoveFirst
    While Not rsLoop.EOF
      strSQL = "INSERT INTO tblADep_PROJ_EXP_SUM ( ACCOUNT_ID, EMP_INITS_NM, EMP_LAST_NM, EMP_SER_NUM, EXP_BEGIN_DT, EXP_BILL_BEGIN_DT, EXP_BILL_END_DT, EXP_END_DT, INVOICE_TXT, PROCESSED_DT, STD_CHRG_AMT )"
      strSQL = strSQL & " SELECT ACCOUNT_ID, EMP_INITS_NM, EMP_LAST_NM, EMP_SER_NUM, EXP_BEGIN_DT, EXP_BILL_BEGIN_DT, EXP_BILL_END_DT, EXP_END_DT, INVOICE_TXT, PROCESSED_DT, STD_CHRG_AMT FROM BMSIW_PROJ_EXP_SUM_V"
      strSQL = strSQL & " WHERE ACCOUNT_ID ='" & rsLoop!ACCOUNT_ID & "' "
     
      db.Execute (strSQL), dbFailOnError
     
     rsLoop.MoveNext
    Wend
    rsLoop.Close
    Set rsLoop = Nothing
    Set db = Nothing
     
    TestLoop_Exit:
       Exit Function
       
    TestLoop_Err:
       MsgBox Err.Description & " in TestLoop"
       Resume TestLoop_Exit
    End Function
    I had thought using currentdb().Execute would prevent me from getting primary key violations i.e. would skip and just enter valid records.

    However, code is failing due to primary key violations.

    Any help would be greatly appreciated !

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try with just

    db.Execute (strSQL)

    which should silently fail and just insert the valid records.
    Paul

  3. #3
    Join Date
    Sep 2013
    Posts
    6
    yes thought that but wasn't sure if that would give me any issues if any other errors came up that I did want to be notified of

    I only want to ignore key violations

Posting Permissions

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