Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014

    Unanswered: After Execute question

    I am running Access 2010. I have set up an append query to append data to a SQL table with data entered in an Access table. I am looking to delete all rows once the comand to append the data to the SQL table has been executed. Can someone help me out with the VB script needed for this? Below I will lay out step by step what is happening.

    User opens data entry form.
    User enters all required data and closes form.
    User then executes trasnmit comand to update SQL table.
    The final step would be delete all rows from the Access table.

    Essentially what I am trying to avoid is duplication of data. I noticed during testing that each time the data was transmitted that all rows were being transmitted, even the rows that had been previously transmitted.

    Thank you in advance for any help

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

    CurrentDb.Execute "DELETE* FROM TableName"

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    as belt and braces you may also want to consider if you can deploy a unique index constraint on the SQL (server?) table, using a PK derived from the Access table

    and or have a timestamp in the access table, run the update process as a batch job, overnight using say windows scheduler and only post data after a specific date and time. record the last itme the process ran and ONLY post rows after that time
    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