Results 1 to 5 of 5
  1. #1
    rd.prasanna is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    25

    Updating the Existing record with new data

    Hi All,

    Im findling little defeculty in importing data From excel to Access,
    My concern is that, I have a data sheet which need to be uploaded on a monthly basis and the data records will be updated month on month bases.

    e.g its like a Monthly test results for the student... student record will remain same throught the year but marks or the percentage will be added monthly bases.
    and i dont want to link the excel to access cos every year the students will be changing same way data records will be getting changed yearly basis

    is there any way to find duplicate and update the new data to it
    if i want to import/Impliment the same in access how to do this...

    Thanks in Advance
    Regards,


    Prasanna

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    Establish link to the sheet and treat it as a table. Use it as source for UPDATE and INSERT sql actions.

    If linking is not practical, only recourse I know is VBA procedure to manipulate the sheet as an object in code to import 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.

  3. #3
    rd.prasanna is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    25
    Thank u for your reply June,

    I know that linking the file is the best option but here we are getting data from all the data so what thought like uploading in one shot...
    any alternate would be much appreciated.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    Options:

    1. linking spreadsheet and running INSERT and UPDATE sql actions (code can be used to help automate)

    2. code that executes TransferSpreadsheet method (this offers the least control over what is imported and does not allow for UPDATE, it's all or none approach)

    3. VBA code that opens spreadsheet as an object then reads each row of sheet and imports/updates only when certain criteria are met, somewhat advanced programming effort but a common topic. Start with review of:
    http://forums.aspfree.com/microsoft-...el-413629.html
    http://forums.aspfree.com/microsoft-...le-413493.html
    Last edited by June7; 10-01-2013 at 03:53 PM.
    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.

  5. #5
    rd.prasanna is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    25
    Thank u june7..

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

Similar Threads

  1. Add new data to existing record
    By chelsyray in forum Queries
    Replies: 2
    Last Post: 10-04-2012, 08:42 AM
  2. Replies: 13
    Last Post: 09-17-2012, 08:46 PM
  3. updating existing report with new data in table
    By newtoaccess123 in forum Reports
    Replies: 2
    Last Post: 10-18-2011, 09:50 AM
  4. Replies: 4
    Last Post: 05-30-2011, 08:20 PM
  5. Replies: 0
    Last Post: 02-25-2011, 09:40 AM

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