Creating Loop

[Rookie]

I have a set of queries that I would like to incorporate in a loop process so
that the logic is complete once all records have been updated.

Below are the queries in order:

Delete_BO_DatabaseTable_1_BatchProcess
Delete_BO_DatabaseTable_2_BatchProcess
Create_BO_DatabaseTable_1_BatchProcess
Create_BO_DatabaseTable_2_BatchProcess
Create_BO_DatabaseTable_3_BatchProcess
Update_BO_DatabaseTable_BatchTable_1
Update_BO_DatabaseTable_BatchTable_2
UpdateKeyNumbersWithBatch
Update_BO_DatabaseTable_BatchTable_2_Post

The query named “UpdateKeyNumbersWithBatch†is updating a tabled titled
“BO_DatabaseTable†and the field that is being updated is called
“BatchNumberFieldâ€.

I basically want the loop to run the process and end while all the records
have a value in the “BatchNumberFieldâ€.

Please let me know if this doesn’t make sense…

 

[pietlinden]

I have a set of queries that I would like to incorporate in a loop process so
that the logic is complete once all records have been updated.

Below are the queries in order:

Delete_BO_DatabaseTable_1_BatchProcess
Delete_BO_DatabaseTable_2_BatchProcess
Create_BO_DatabaseTable_1_BatchProcess
Create_BO_DatabaseTable_2_BatchProcess
Create_BO_DatabaseTable_3_BatchProcess
Update_BO_DatabaseTable_BatchTable_1
Update_BO_DatabaseTable_BatchTable_2
UpdateKeyNumbersWithBatch
Update_BO_DatabaseTable_BatchTable_2_Post

The query named “UpdateKeyNumbersWithBatch” is updating a tabled titled
“BO_DatabaseTable” and the field that is being updated is called
“BatchNumberField”.  

I basically want the loop to run the process and end while all the records
have a value in the “BatchNumberField”.

Please let me know if this doesn’t make sense…

if the structure of your tables that you import into do not change, I
would just delete the contents. (use a delete query with no where
clause.) Then do something like this.

DoCmd.SetWarnings False 'shut off warnings so you don't get the "you
are about to..." messages.
DoCmd.OpenQuery "Delete_BO_DatabaseTable_1_BatchProcess"
DoEvents ' force synchronous execution of queries so the complete
instead of just starting before going on to the next step
DoCmd.OpenQuery "Delete_BO_DatabaseTable_2_BatchProcess"
...
DoCmd.SetWarnings True

You could update the screen after each query executes, so the user
knows what's going on/gets feedback.

 

[Rookie]

Thanks for the info, but how can I force the process to repeat itself until
the field that I am updating contain values?

The table I'm updating contains over 150,000 records, and I'm updating each
records one at a time, but instead of using a macro to repeat the process x
number of times, I wanted to write a code that would run the process until it
completes updating the entire table one record at a time...

 

[pietlinden]

Thanks for the info, but how can I force the process to repeat itself until
the field that I am updating contain values?

The table I'm updating contains over 150,000 records, and I'm updating each
records one at a time, but instead of using a macro to repeat the processx
number of times, I wanted to write a code that would run the process until it
completes updating the entire table one record at a time...








- Show quoted text -

Is there a reason you can't do the updates a set at a time instead of
a record at a time? A single record at a time for 150K records will
take forever! Is there a specific reason you can't use a batch?

If you need this to succeed or fail as a unit, you should execute all
the queries inside a transaction so that if something fails in the
middle, you can just roll the transaction back.