Re: Indicating Stored Procedure progress on Form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Sylvaine,

Back in December you helped me with this, but I just returned to the task..

1. To recap, I'm executing a series of SP's within one master SP, and want
to display a status table on a form as each individual SP finishes.
Each individual SP writes a line to a status table when it completes.

2. In response, you replied as below at the end of this message, and I have
implemented the separate connections and READ UNCOMMITED.

3. I have now have an unbound form to display the status table, with a
Separate ADO connection than main ADP connection, with a timer event and a
me.requery ever 5 seconds. The form's recordset is based on SELECT * FROM
tblStatusWITH(READUNCOMMITTED).

4. The master SP is also called through a separate connection from the main
app connection.

5. RESULT SO FAR: The Status form properly displays, line by line as each
individual SP completes, *** ONLY IF *** I run the master SP from Query
Analyzer.

However, if I run the master SP from within Access, the Status form does not
display until the entire Master SP completes, even though the Master SP is
being executed through a separate connection.

Note: I also tried this with UPDATE instead of INSERT, with same result.

6. QUESTION: Is there any way to get this to work executing the Master SP
from within Access, or will such an effort always tie up Access because it
is a synchronous operation?

Thanks for any insight you can give.

Regards,

Alan




"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eGWYiM6IHHA.3872@xxxxxxxxxxxxxxxxxxxxxxx
Two possibilities:

1- You are using the same connection for calling your SP than the
connection used by Access to open the second form. Try opening a separate
connection to query your table.

2- A transaction is open when the master SP is called, all subsquent
writes are then part of this transaction and won't be visible from the
outside until the transaction is committed. Try the Read Uncommited
isolation level for reading your table (cannot be done for a bound form
under Access, so you will have to directly open a recordset from VBA).
However, I don't know if this will work in your case.


.

Relevant Pages

  • Re: Indicating Stored Procedure progress on Form
    ... Sylvain Lafontaine, ing. ... Separate ADO connection than main ADP connection, ... The master SP is also called through a separate connection from the ... SP is being executed through a separate connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Indicating Stored Procedure progress on Form
    ... To recap, I'm executing a series of SP's within one master SP, and want ... Separate ADO connection than main ADP connection, with a timer event and a ... The form's recordset is based on SELECT * FROM ... SP is being executed through a separate connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Indicating Stored Procedure progress on Form
    ... Separate ADO connection than main ADP connection, ... The master SP is also called through a separate connection from the ... SP is being executed through a separate connection. ... writes are then part of this transaction and won't be visible from the ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Indicating Stored Procedure progress on Form
    ... Is this going in the master stored procedure calling the individual stored ... Sylvain Lafontaine, ing. ... Separate ADO connection than main ADP connection, ... Master SP is being executed through a separate connection. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Big knowledge gap wrt TCP/IP
    ... forward whatever I recieve - the connection ... >From the POV of the power-line communication mechanism, ... We have a master machine in the background, ... Now, although slave1 needs to talk to slave2, there is no way to ...
    (comp.lang.tcl)