Run sql server sproc in Access form w/o locking up?

[zachster17]

Hi everyone,

Is there anyway to run a sql server stored procedure from access (using the ADODB.Command object) without locking up the access application?

For example, I want a list to populate when a user opens a form using a stored procedure. The only thing is that the stored procedure takes a little bit (~10 seconds) to run completely. I have it setup to display a label saying 'loading list...' before the sproc runs and it goes away after--however, you can't do anything else on the form while the sproc is running as always, but I'd like the user to still be able to click/interact with other objects on the form.

Is there a certain property I can utilize to allow the sproc to run in the background?

Thanks,

Zach

[nico5038]

When a command is triggered Access will wait :-)
A workaround might be the filling of a temp tabel in MS SQL with the proc and link a table in Access to that for your list. Thus the (old) data will show and a refresh can be triggered from code e.g. by pressing a button.
You might even (when the data is rather "static") trigger the refresh when opening the database. Consecutive openings of the form will be faster this way..

Nic;o)

[ADezii]

Tell Access not to wait for the Command Object to complete execution, and try running the Stored Procedure Asynchronously:

<Your Command Object>.Execute Options:=adAsyncExecute

[zachster17]

Tell Access not to wait for the Command Object to complete execution, and try running the Stored Procedure Asynchronously:

<Your Command Object>.Execute Options:=adAsyncExecute

I used the adAsyncExecute and read more about it and it is working just how I wanted. Thank you!

[ADezii]

I used the adAsyncExecute and read more about it and it is working just how I wanted. Thank you!

You are quite welcome.