asked on

pause code in sql2008 through Access07

I do have a code that executes Stored Procedures instructed in Access07 step by step. This all worked fine with SQL2000, but I now would like to have same with SQL2008. the problem now is, that it goes through SP-Executing withouth waiting a SP is finished and therefore, a later SP will be executed withouth having the newest data and will then bring old/wrong results. How can I implement that it waits till SP-executing is finished before executing the next one? Can I implement a pause-code?
thx and merry x-mas
Kongta

Me.frmKursupdate_StatusInfo = "Uebertrag in DB...."
Me.FX_Anzeige.ForeColor = RGB(255, 200, 100)
DoEvents

Set c = New ADODB.Connection
c.ConnectionString = "Driver={SQL Server};Server=" & Srv & ";Database=" & DB & ""
c.Open

Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = c
    .CommandText = "sp_DTS_xls_FX"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 0
    
    .Execute
End With

c.Close
Set c = Nothing
Set cmd = Nothing

Me.frmKursupdate_StatusInfo = "....erfolgreich."
Me.FX_Anzeige.ForeColor = RGB(100, 255, 100)
DoEvents
'------------------------------------------------------------------------------

'Uebertraegt tblIndex-------------------------------------

Me.frmKursupdate_StatusInfo = "Uebertrag tblIndex...."
Me.Index_Anzeige.ForeColor = RGB(255, 200, 100)
DoEvents

Set c = New ADODB.Connection
c.ConnectionString = "Driver={SQL Server};Server=" & Srv & ";Database=" & DB & ""
c.Open

Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = c
    .CommandText = "sp_DTS_xls_Index"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 0
    
    .Execute
End With

c.Close
Set c = Nothing
Set cmd = Nothing

Me.frmKursupdate_StatusInfo = "....erfolgreich."
Me.Index_Anzeige.ForeColor = RGB(100, 255, 100)
DoEvents

Open in new window

tbsgadi

Try putting Doevents between the SPs

Gary

Kongta

ASKER

I've placed DoEvents after End With but doesn't improve. I have one SP that takes about 60s and is the basis for the following SP. This code worked fine in Access07/SQL2000, it was easy to see on what SP the system actualy was, but now it goes "wooom" and finished

tbsgadi

Does it work if you set a breakpoint in the middle?

Kongta

ASKER

Breakpoint? Each stored procedure stands for itself and will be called by Access07 each by each. How do I set a breakpoint in Access-coding? I googled and seen a timer-instruction but while reading thought that this might not work.

tbsgadi

I just meant to pause before the second SP and then continue. (manually)

tbsgadi

You can try using the sleep API

Kongta

ASKER

the whole code will be executed by one click-event and should run through without any interaction. I don't know why in sql2000 this has worked fine, meaning that Access07 waited for each SP finished before steping to the next one. What do you mean by sleep API, haven't found anything in hlp-file. Isn't there any code for access saying pause for 30s before moving on?

ASKER CERTIFIED SOLUTION

tbsgadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kongta

ASKER

ok, this looks about what I'm looking for. I'am aware where I have to place the code for calling sSleep

Const cTIME = 1000 'in MilliSeconds
Call sSleep(cTIME)

but where I have to place the PrivateSub, do I have to create a procedure? I tried but doesn't work

tbsgadi

You put the Declare at the top of the module under Option..

Kongta

ASKER

perfect, now it works and gives back the results wanted
many thx and enjoy x-mas
rgds
Kongta

tbsgadi

Glad to help!