Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011

    Question Unanswered: How to update records in Access using data in excel


    I have a query in acess which brings data from different tables. Each month I print this query and I send to a person that will modify the numbers (manually - yes with a pen) and give it back to me. Then I update it manually (in the computer) and print it back to verify.

    Once I have the final version done, I have to put the updated data into access again. And this is the part I would like to automate (since its not possible to automate the first step because the other person is a retarded on computers and want to use a pen).

    So how can I import the data I updated in excel to access? As far as I saw I can only import data into a table, not update using a query. Plus while we are making this manual updates, the data in access is changing. So copy and paste is not an option since the order of the records in the query will be heavily different.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Why not let that person have a limited access to your application and get then to enter the data directly? Access is a multi user capable db...

    failing that make certain you have the primary key for each row embedded in your excel sheet, so that when the data comes back to you you can identify the row(s) in the table to be updated

    if this is a regular process then If I were you I'd write a fully formatted spreadsheet , locking down clls that cannot be updated (so the user cannot inadvertently tinker with the spreadsheet. its virtually impossible to stop deliberate tinkering with a spreadshet, can you stop someone remvong the locking,m tinkering with the db ten re locking, but thats a different story

    then write a VBA process which reads the data in from a spreadsheet and updats your db. but take adequate precautions against data corrupion., in my books its rarely smart to let users modify data in a spreadhseet that is to go back into a db, becuase its too easy to change stuff without realising or appreciating the consequences.

    as part of your import process build in validation checks, decide how you want to handle errors. there's two choices,. either reject all changes untill no errors, or accpet what you can (and say put the errors in to a temporary location within the DB, until the user clears the errors. not onlky shoudl your import process validate on entry, but check that 'stuff' is where it should be (ie check column & row headings, embedd a version nuimber inside your spreadsheet.

    if ytou dont' want to go down the route of writign a spreadsheet, and merely do an export to xls, then embed NOT just the priamry key for ewach roiw, but also some form of check digit validation on the PK, so if the PK gets changed you can identify it. Natch dont' let your users know what your check digit calculation is in case someone decides to maliciously modify something.

    if you do automate the process document it well/extensively nto just for when you leave bu when you have to pick out the errors at daft o'clock at night under a time pressure. validate, validate, and validate before doing anything.

    if you have a choice deploy an Access form and given them direct access tot he data within the DB. If you want to you can deploy a specific form within a front end which only allows the users specific access.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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