Need timer help - Access to/from SQL

[greaseman]

I've got a project with several code modules that's supposed to work like this:

01) via code, run an SQL process to copy a record from one SQL table to another SQL table.

02) via code, run an SQL process to update the original record in the first SQL table.

03) via code, run an SQL process to take the modified record from the first SQL table to another SQL table.

04) via code, run an SQL process to delete the modified record from the first SQL table.

I have encountered problems when this "stuff" runs from within Access 97. The first and last steps appear to run well, but the 2nd and 3rd steps don't seem to run at all.

When I hard code the word "Stop" in the module, and then step through the code, everything works great. That led me to perhaps it was a timing issue. I then placed code in my module to cycle through a counter, to "slow down" the module. That only worked sometimes, but not at othe times.

Has anyone out there encountered this kind of headache? If so, what did you do to work through it?

As always, your responses are appreciated! This forum is much more informative than what you can glean from the Microsoft site.

Thanks again!!!

 

[ChrisO]

G’day greaseman.

In this thread I tried to raise a possible solution but don’t know the outcome.

It’s even more difficult to test in your case because one needs a number of slow dependant tasks to perform.

At this stage I wouldn’t be too concerned about using a form timer event in a public module…that should be easy enough. (Create another form solely for the purpose of using its timer.)

First step seems to be getting the timer to sequence the SQL processes correctly.
Next step would be making the sequencing public.
Final step would be trying to make the whole process of sequencing dependent SQL’s reusable. (Something along the lines of a user defined data type that can hold the form name, timer interval, SQL references and anything else that may be required.)


Almost entirely ‘airware’ at the moment but should be possible.

Worth a shot???

Regards,
Chris.

 

[ChrisO]

Step one seems to work…first draft.

Option Explicit
Option Compare Text

Private Const conOuterLoop As Long = 2
Private Const conInnerLoop As Long = 20000


Private Sub cmdStartTheProcess_Click()
    
    Me.txtResultOfTiming = ""
    Me.txtProgress = ""
    DoEvents

    Me.TimerInterval = 1
        SlowProcess1
        SlowProcess2
        SlowProcess3
        SlowProcess4
    Me.TimerInterval = 0
    
    Me.txtResultOfTiming = "SlowProcess1 through SlowProcess4 finished before the timer."

End Sub


Private Sub SlowProcess1()
    Dim lngOuter As Long
    Dim lngInner As Long
    
    Me.txtProcess = "SlowProcess1"
    
    For lngOuter = 1 To conOuterLoop
        For lngInner = 1 To conInnerLoop
            Me.txtProgress = CStr(lngOuter) & "  " & CStr(lngInner)
            Me.Repaint
        Next lngInner
    Next lngOuter

End Sub


Private Sub SlowProcess2()
    Dim lngOuter As Long
    Dim lngInner As Long
                         
    Me.txtProcess = "SlowProcess2"
    
    For lngOuter = 1 To conOuterLoop
        For lngInner = 1 To conInnerLoop
            Me.txtProgress = CStr(lngOuter) & "  " & CStr(lngInner)
            Me.Repaint
        Next lngInner
    Next lngOuter

End Sub


Private Sub SlowProcess3()
    Dim lngOuter As Long
    Dim lngInner As Long
                         
    Me.txtProcess = "SlowProcess3"
    
    For lngOuter = 1 To conOuterLoop
        For lngInner = 1 To conInnerLoop
            Me.txtProgress = CStr(lngOuter) & "  " & CStr(lngInner)
            Me.Repaint
        Next lngInner
    Next lngOuter

End Sub


Private Sub SlowProcess4()
    Dim lngOuter As Long
    Dim lngInner As Long
                         
    Me.txtProcess = "SlowProcess4"
    
    For lngOuter = 1 To conOuterLoop
        For lngInner = 1 To conInnerLoop
            Me.txtProgress = CStr(lngOuter) & "  " & CStr(lngInner)
            Me.Repaint
        Next lngInner
    Next lngOuter

End Sub


Private Sub Form_Open(ByRef intCancel As Integer)

    Me.lblProcess.Caption = "<< Current Process 1 to 4"
    Me.lblProgress.Caption = "<< Current Progress 1 to " & CStr(conInnerLoop)

End Sub


Private Sub Form_Timer()

    Me.TimerInterval = 0
    
    [color=green]'  You should never reach here.[/color]
    Me.txtResultOfTiming = "Times Up...It Failed!"
    Stop

End Sub

Regards,
Chris.

 

[greaseman]

That dratted timer issue

Thanks for your reply! What you suggested and what you posted for a possible coding solution to my timer headache is close to what I ended up doing. I placed timer code around each of my queries, and my results seem good.

I'll "play around" with your suggestions also.... two heads are usually better than one, and I appreciate your suggestions.

Have a g'day.