[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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

0
Kongta
Asked:
Kongta
  • 7
  • 5
1 Solution
 
tbsgadiCommented:
Try putting Doevents between the SPs

Gary
0
 
KongtaAuthor Commented:
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
0
 
tbsgadiCommented:
Does it work if you set a breakpoint in the middle?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
KongtaAuthor Commented:
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.
0
 
tbsgadiCommented:
I just meant to pause before the second SP and then continue. (manually)
0
 
tbsgadiCommented:
You can try using the sleep API
0
 
KongtaAuthor Commented:
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?
0
 
KongtaAuthor Commented:
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
0
 
tbsgadiCommented:
You put the Declare at the top of the module under Option..
0
 
KongtaAuthor Commented:
perfect, now it works and gives back the results wanted
many thx and enjoy x-mas
rgds
Kongta
0
 
tbsgadiCommented:
Glad to help!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now