insert_record [DAO]

' Might be run after add_index.bas
option explicit

sub insert_record()

  dim access as Access.Application
  dim db     as Dao.Database
  dim rs     as Dao.RecordSet

  dim mdb_file_name as string 
  mdb_file_name = "c:\\temp\\dao_test.mdb"
  set access = new Access.Application

  access.OpenCurrentDatabase(mdb_file_name)
  set db = access.CurrentDb

  set rs = db.openRecordSet("some_table", dbOpentable)
 
  ' First Record

  call insert_a_record (rs, #08/28/1970#, 10.02, "some text", "more text")
  call insert_a_record (rs, #01/01/2001#, 11.11, "one"      , "Apple"    )
  call insert_a_record (rs, #02/02/2002#, 22.22, "two"      , "Pear"     )
  call insert_a_record (rs, #08/28/1970#, 10.02, "some text", "more text") ' Same record as first

  set db      = nothing
  set access  = nothing

end sub

sub insert_a_record (              _
  rs           as dao.recordSet ,  _
  date_col     as date          ,  _
  curr_col     as currency      ,  _
  txt_col      as string        ,  _
  col_5        as string) 

  on error goto insert_fail

    rs.addNew
  
    rs("aDateColumn"    ) = date_col
    rs("aCurrencyColumn") = curr_col
    rs("aTextColumn"    ) = txt_col
    rs("fifthColumn"    ) = col_5
  
    rs.update

    exit sub

  insert_fail:
    dim err_desc as string

    err_desc = "record" &              vbCrLf & _
               "  "     & date_col  &  vbCrLf & _
               "  "     & curr_col  &  vbCrLf & _
               "  "     & txt_col   &  vbCrLf & _
               "could not be inserted" 

    select case err.number
      case 3022
        err_desc = err_desc & vbCrLf & "because a unique index was validated"

      case else
        err_desc = err_desc & vbCrLf & "Error No: "   & err.number & vbCrLf & _
                                       "Error Text: " & err.description
    end select

    msgBox err_desc

end sub