Results 1 to 4 of 4
  1. #1
    munchkinz is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    2

    Newbie Update table from Excel??

    Hi, I'm a complete newbie to access and need some help.



    I have a csv file of names addresses etc each month I receive an updated csv file with the details of anybody who has moved address.

    Is there a way to update the original table and replace the existing data with the new data. The only field the same in the 2 csv files are the name fields.

    Help please,

    Thank you

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The field names are always the same? If the file name is also the same one way might be is to set a link to the csv and run update query. However, names make poor unique id for linking datasets.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    munchkinz is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2015
    Posts
    2
    Thanks for your reply.

    The file names are different. but the field names are the same in both files.

    Each row in the update has a letter (D,M or N) to determine what should happen to that row for example D=delete M=modify N=new.

    Heres an example of what I have.

    Main database
    ChangeTypeID Forename Surname Address1 Address2
    John Doe 123 street anywhere
    Jane Doe 123 Avenue anywhere

    Update I need to insert to modify the main database
    ChangeTypeID Forename Surname Address1 Address2
    D John Doe 123 street anywhere
    M Jane Doe 123 street anywhere


    based on my example, john doe would be deleted and jane doe would be modified to 123 street instead of avenue.

    The files are given to me by an outside source and their for I cannot change the format of them, and the only identifiers the same are forename and surname.

    Can this be done in access? or is there a better way to do this.

    I hope I've explained this a little better.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Names and addresses make very poor unique identifiers. However, what you want could be accomplished. The names and addresses would have to be used in combination to identify the records.

    You can name the received file whatever you want. As long as the same name is used, just replace the older file and the established link will still work.

    Otherwise, will involve lots of VBA code to read the CSV file one line at a time and write/edit data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newbie Import. Update Field of Existing Records from Excel File
    By gedwards913 in forum Import/Export Data
    Replies: 8
    Last Post: 03-12-2015, 07:53 PM
  2. Replies: 4
    Last Post: 11-27-2014, 10:20 AM
  3. Importing Excel - Update table field with the worksheet name
    By maggiemago3 in forum Import/Export Data
    Replies: 1
    Last Post: 08-22-2013, 04:51 PM
  4. VBA to update Products Table from excel file
    By Mclaren in forum Programming
    Replies: 2
    Last Post: 07-26-2012, 12:14 AM
  5. Replies: 1
    Last Post: 01-16-2012, 06:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums