Make access wait till sql is done

[Pauldohert]

I want to run a sp in sql server - which will create records - then I want to requery my form in access to show those records. How do I ensure that my requery runs consecutive to my sp.
Currently it runs concurrently.

I am running the sp by running a pass thru query which calls the sp?

Ta

 

[KeithG]

have you tried running your sp in a transaction in access?

 

[SQL_Hell]

I can think of so many different ways to do this, that maybe my brain will explode if I think about it any longer

What is you current data source for the form?
Can you provide the bit of code that does the SP call and requery the form?so I can see exactly whats happening and choose the best method of fixing it for you.

Cheers

 

[Pauldohert]

I chamged from using a ptqry to


Dim wdB As Database
Dim strConnect As String

On Error GoTo Err_Handler
gfnDBConnectStr strConnect

Set wdB = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect)
wdB.QueryTimeout = 300
wdB.Execute pSQL, dbSQLPassThrough


Ta!

 

[SQL_Hell]

Hi there


Does it work now then?

 

[SQL_Hell]

Hi,

I guess I wasn't much help here

 

[Pauldohert]

Sorry yes - it now appears to be working OK.

Thanks

 

[Banana]

Actually, if you want all of your queries to always run syncrhonously, you can edit Jet's registry setting... something like AllowAsync=NO, then the Jet will now wait for the backend to complete the SQL command before moving on.

That said, be prepared for serious performance hit if you opt to do this. Jet runs asynchronized for a good reason- by getting a few at time, your users seem "instantaneous" loading time and can navigate around a bit, while Jet is building the recordset rows by rows in background.

If you just want syncrhonization for only one query, I am sure there is a property somewhere in DAO query... something like "StillExecuting" which you can add a loop:

Do until qdf.StillExecuting=False
    'Do nothing
Loop

Read the help file for more information on that property.