Not Responding on long processes

[Movian]

Hey,
In my database i have several functions that can take a VERY long time to process (some as long as an hour). Unfortunatly when these run after a few minutes they appear as not responding. What i would like to do is put up a progress bar unfortunatly that too stops responding until the process has finsihed. IF i was working in c# i would use multiple threads and or a background worker. However in Access i don't have that luxury... any thoughts on a solution for this problem ?

[OBP]

Is it on a Form?
Can you use the Form Timer?
Any reason that they take so long?

[Movian]

ok, well let me give you an example.

The database has well over 100 tables in it. I have one procedure that opens up a table and then for each record in that table (possibly well over several thousand records) it systematically opens each table in the database, checks if there is a corresponding id to the id in the first table. If there is not it creates a record in that table then moves on the next table. If there is it does nothing and closes the table.

*Edit* i forgot to mention if it does add a record to the table then it checks through a default values table and puts in any applicable default values for this new record aswell. (this is so that the end user can customize some defaults even though the system is delivered as an accdr).
*end edit*

i was wondering about having a generic progress bar form. i have put in a generic update function

Public Sub UpdateProgress(TotalVal As Integer, CurrentVal As Integer)
If CurrentVal >= TotalVal Then
DoCmd.Close acForm, "ProgressBar"
Else
Me.Bar.Width = (Me.Total.Width / TotalVal) * CurrentVal
End If
End Sub i was hoping to call this with forms("ProgressBar").Updateprogress var1,var2

However while this works for a short while. The database inevitably locks up as Not Responding. Are you suggesting to change this to have the procedure maybe update 2 globals for total and progress then on a time event have it update the progress bar ?

[geekgirlau]

Wow.

Well at first glance I'd suggest that you can probably cut out of lot of this with the setup of your tables.

Set the indexes so that each table will not allow you to append a duplicate record. That way you don't have to check the table first, just append away as the duplicates will automatically fail.
Secondly can you not ensure that the default values are already in the record that you are inserting rather than running a separate process for this?In terms of the timeout, any progress bar is going to struggle. One way around this is to have a small popup form appear with a static message, explaining that this process will take about a squillion years, nope the database hasn't crashed and please hold your horses. You can try a progress bar or timer on the popup form, but you will find at some point it just stops refreshing (hence the static message).

[Movian]

While i apriciate the input, unfortunatly as i mentioned this is an example. I have several functions that take some considerable time, while i could go through each and optimize them to be as effecient as possible it dosnt really get to the core of the problem.
Are you saying that there is no way to stop the system thinking that microsoft access is not responding if it takes to long to process a particular process? i will look into your anti duplicates suggestion. With regards to the defaults, i have it setup like this so that the end user can apply a default value to particular fields. As they cannot change the actuall code or propertie from an accde/accdr i setup a lookup table that stores all the default values and inputs them upon the creation of a new record in a table.

But the overall answer appears to be that there is no way to prevent the system from setting to not responding if you happen to want the system to go through a lengthy process..

[OBP]

I would try the timer version, unless when you have nothing else happening (like your status bar) it still crashes with "Not Responding". In which case nothing is going to work.

I agree completely with geekgirlau that using the correct Unique key fields with Append & Update Queries is far faster than using VBA code and should overcome most of your problems.