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 ?
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 ?
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 ...
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
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.........
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)"
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....
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"......
Wow Perfect Thank You
I would not have been able to do that myself.
Merry Christmas
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?
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.
Thank you. Appreciate
Hi Steve
It is working if I only choose 50 Rows each time.
Thank you for your time and help
Merry Christmas
Excellent! Glad you got it working..
Good luck with your project...