Can I useVB 6 to mimic the make-table & delete queries in Access

[ImGettingThere]

I am developing an application, using VB6, that, in a nutshell, will be used to import data from several spreadsheets then interrogate the data to produce statistics.

I have set up one command button to import the main set of data. The user will save 5 or 6 spreadsheets into a folder. Upon clicking the button the program scan through the contents of the folder, every time it comes across a spreadsheet it will lift the data out of it and place into a back end Access database. This command button also handles the creation of the database, the creation of the table and the formatting of the table structure. On average i'm looking at importing between 140,000 - 150,000 records each month. Takes just over a second to complete the import.

Here's where I'm having my problem. The user will use a 2nd command button to import a second lot of data into another table. This data needs to be removed from the main data table. I had planned on doing the equivalent of an Access make-table query and copying the matching records, in bulk, from the main table into a 3rd table. The 3rd table is to used for validation purposes. Once the validation checks are completed the program will automatically delete, again in bulk, the matching records from the main table.

I do not want to loop through the main table line by line to extract and delete the records as I suspect it would take forever. Is there a way in which I can do this quickly in bulk?

Code examples would be greatly appreciated. Obviously I've not given any specific data details so Idont expect to see full SQL statements but any other pointers would be ideal.

I apologise in advance if I've not explained myself very well.

Regards

 

[SBerthold]

It really depends what criteria the validation is based on. Most often, you should be able to use enough criteria in the WHERE cluase of one (or more) Bulk DELETE or UPDATE sql statement(s) to satisfy the Validation and eliminate or mark-for-deletion the records in question.

For instance: if the records in the second table are to be removed from the first table, and the is a common key among both tables, then a DELETE statement could work:

DELETE FROM myFirstTable WHERE myKEYField IN (SELECT myKEYField FROM mySecondTable)