Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Port Macquarie, New South Wales, Australia

    Unanswered: Importing and Updating an Access 2003 table

    Hi, I have borrowed this code from a 'prevent duplicates 2000' Microsoft access database application:-

    Private Sub PersonCNI_BeforeUpdate(Cancel As Integer)

    'Code sample courtesy of srfreeman

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.PersonCNI.Value
    stLinkCriteria = "[PersonCNI]=" & "'" & SID & "'"

    'Check tblPerson table for duplicate PersonCNI
    If DCount("PersonCNI", "tblPerson", stLinkCriteria) > 0 Then
    'Undo duplicate entry
    'Message box warning of duplication
    MsgBox "Warning CNI Number " _
    & SID & " has already been entered." _
    & vbCr & vbCr & "You will now been taken to the record.", vbInformation _
    , "Duplicate Information"
    'Go to record of original Student Number
    rsc.FindFirst stLinkCriteria
    Me.Bookmark = rsc.Bookmark
    End If

    The original code contained strtudentDetails but I have changed that to PersonCNI for my own purposes.

    Anyway, I have a question about importing fresh data into my "tblPerson". That table has the following fields;
    PersonCNI (this is the primary key with the aim of preventing the same person being entered into the table more than once), Person1stName, Person2ndName, PersonSurname, LKAUnitLevelNo, LKAStreetNoFrom, LKAStreetNoTo, LKAStreet, LKASuburb (LKA stands for last known address).
    The data in this table comes from a live system which I can export either into a csv file, or excel etc. I would like to ask this question. I would then like to import that fresh data into the "tblPerson".

    If the above code prevents duplicate data from being entered into the table because of the existance of the UNIQUE number of the persons "PersonCNI" field. Is it still possible for me to update the persons LKA details to be updated in the "tblPeson" if that "PersonCNI" already exists. So if the "PersonCNI", "Person1stName", "Person2ndName", "PersonSurname" fields are seen in the new data, is it possible during the import process to only update the relevent fields that relate to a newer address. If the fresh import contains new persons not yet entered, is it possible to allow those fresh person details to go into the table without a problem?



  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6

  3. #3
    Join Date
    Aug 2007
    Port Macquarie, New South Wales, Australia
    Quote Originally Posted by pbaldy
    Paul, thank you, that was a great little learning process for me. Tim

Posting Permissions

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