Results 1 to 13 of 13
  1. #1
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34

    Query delete in batches

    Hi



    I need a delete query to run in batches of 100 rows, until no more rows.

    Code:
     DELETE *
    FROM SAP_Import2
    WHERE not exists (Select 1 from Fin WHERE SAP_Import2.title= Fin.title);
    



    Can you help me getting started ? Maybe with a VBA loop ?


  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Why not delete all at once?
    why 100 then the next 100, and next...

    Q1:
    select top 100 from table

    Q2:
    delete * from Q1 where (field in ...

  3. #3
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34
    Hi

    The query compare 2 tabels one connected to Sharepoint.

    If I keep the Rows limited I dont have any problems, but if I run the full query sharepoint gives me problems

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I played around with some VBA - finally got it to work. You didn't provide many details, so look at the attached example. I have a 1 second loop delay between deletes.
    There are 1000 records where the field "Title" has "Title1" and 1000 records for "Title2".

    There is code in Module2 to append more records - just change the value for the field Title (see code).

    This is only an example, so you'll have to modify the code for your requirements.


    Good luck with your project.........
    Attached Files Attached Files

  5. #5
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34
    Hi Steve Thank You

    I have 2 table SAP_Import2 and Fin


    My Query compare 2 tabels SAP_Import2 and Fin (Delete the records in SAP_Import2 that dont exists in Fin)
    Each table have about 3000 rows, changing every day.



    How Can I Change the Code in the module1 to:





    strSQL = "DELETE FROM (SELECT TOP " & intNumRecords & " * FROM SAP_Import2 WHERE not exists (Select 1 from Fin WHERE SAP_Import2.title= Fin.title)"

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would post your dB? Or CSV files of the data (Titles) in tables "SAP_Import2" and "Fin"? (so I don't have to spend time trying to create data in the tables)
    Or examples of what you want....

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, try this.

    I created an Unmatched query on Title.
    I added an outer loop (titles), then I did the same inner looping to only delete a specified number of per loop.

    I created two tables with one field - "Title". If you try this and you want to do it again, delete "tblSAP_Import2", copy & paste the "Copy Of tblSAP_Import2" table and delete the "Copy Of Copy Of "at the beginning of the table name.



    Edit: I just realized I prefixed the table names with "tbl"......
    Attached Files Attached Files

  8. #8
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34
    Wow Perfect Thank You

    I would not have been able to do that myself.

    Merry Christmas


  9. #9
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34
    Hi Again

    Do you know what to do?

    If I use you VBA against a table in access it works fine.


    If I use against link to SharePoint I get Run time error 3086.

    I Can delete manual from access, so the link works fine.

    Is there way around this?

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Probably, but I have never used Sharepoint, I don't even know how to connect to Sharepoint , so I don't know what to tell you. I'll have to back out for now. Maybe someone from the Sharepoint forum can help...

    Sorry... Good luck with your project.

  11. #11
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34
    Thank you. Appreciate

  12. #12
    tonyhansson is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Aug 2020
    Location
    Copenhagen, Denmark
    Posts
    34
    Hi Steve

    It is working if I only choose 50 Rows each time.

    Thank you for your time and help

    Merry Christmas

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Excellent! Glad you got it working..

    Good luck with your project...

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

Similar Threads

  1. Replies: 6
    Last Post: 06-21-2018, 12:56 PM
  2. Batches setup in tables
    By AndyC121 in forum Access
    Replies: 30
    Last Post: 07-13-2017, 05:18 PM
  3. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  4. Printing reports in batches
    By mnsemple83 in forum Access
    Replies: 6
    Last Post: 08-16-2011, 03:41 PM
  5. creating batches - please help
    By spectrox in forum Import/Export Data
    Replies: 1
    Last Post: 04-28-2009, 09:44 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