Hep me understand

[Robgould]

I have a question. I have been using access for a couple years now and have written quite a bit of VBA code in that time, but it is all one type of code. Event driven and linear. By that I mean that in response to some evenet code is executed and no other event or activity can occur until that code has completed running. If the code takes a long time, the user has to wait.

Is there a way to seperate the code from the user experience? Can you write code in a way that allows it to run in the background while the user contines their acitivities?

One idea I had was to replicate the database then proceed with code while the user works with the relicated copy. Don't know if this is possible or not.

But even thinking that through, I don't know how I would make code run without the user having to wait for it.

Any help would be greatly appreciated. Thanks.

 

[ghudson]

I do not think that is possible because Access has a hard time doing more than one thing [event, procedure] concurrently.

 

[DB7]

I agree with ghudson,
but I'm just curious Rob,
What processing would you do, for example?
How long will it take to run?
how will it impact the current form?

As you know, OnLoad, OnOpen etc... are somewhat "involuntary" events.
Are these sufficient, to initiate certain processing.(maybe from your start-up form)
Maybe with a doEvents command?

just thinking out load.
I'm curious if running code on an table/form without the focus,
will effect the current Form?

 

[Robgould]

I'm not sure either. I will have to play with it a bit.

What I have is a database that gives the user that option to "synchronize" their outlook contacts with the contacts in the database.

If you select this, I go througugh every contact in the database and:

Check to see if they are in outlook already

If not add them

If they are, update the details

This takes a long time...and longer as the contact list grows. When I tested the code on one or two contacts it worked fine.

I think I just need to re-write the code. Maybe add another field to access when the contact is created. Like my own little archive bit. It is not changed then skip all the update code.

Right now I add the contactid to the field user1 in outlook. That way I can search outlook with my record number. I was thinking about another field that I could set to 1 everytime the contact is added or updated. Then I would have to change it to 0 everytime the contact was changed.

Does this seem like a reasonable approach?

 

[DB7]

I think so offhand, Rob.
Maybe a BOUND, Boolean field (chkEdit),
so each Form_AfterUpdate, it becomes true (single record),

When you call Your "Sync" code recordset.
Only call records, where chkEdit = True.
rec.Open "SELECT .....WHERE chkEdit = True", adOpenForwardOnly,adLockPessimistic

Then after your "Sync" code runs, it makes them all false.

CurrentProject.Connection.Execute _
"UPDATE tblContacts SET chkEdit = False"

 

[Robgould]

Thanks for the input....I'm excited to give it a try.

 

[Banaticus]

Do you throw up some sort of splash screen with some sort of bar that shows the user how much of the work has been done so far? For instance, you could change:
For i = 1 to AWholeBunch
Call DoLotsOfStuffWith(i)
Next i

to

For i = 1 to AWholeBunch
Call UpdateSplashScreen(i)
Call DoLotsOfStuffWith(i)
Next i

I know that if I have to sit and watch something update and I don't have any sort of idea how long it will take, time seems to slow to a crawl and I get very, very bored.

 

[DB7]

Don't forget "DoEvents

For i = 1 to AWholeBunch
DoEvents
Call UpdateSplashScreen(i)
Call DoLotsOfStuffWith(i)
Next i

 

[DB7]

Banaticus, I wasn't thinking,
Why call the code, sooo many times?

 

[Banaticus]

Which code? I presume that you have some sort of loop that you use to do stuff until you hit someLimit. Add code to update the splash screen in each pass through the loop, for instance:
for i = 0 to someLimit
'fill splash screen's bar with i/someLimit l's

So, if someLimit is 50, when i is 25, the splash screen would be 1/2 filled up with l's. If someLimit if 50, when i is 40, the splash screen would be 9/10 filled up with l's.

Count how many l's it takes to fill up your bar, use that to calcualate how many l's are needed to represent 1/2 of the bar filled up.