Make access wait till sql is done (1 Viewer)

Pauldohert

Something in here

Local time

Today, 08:16

Joined

Apr 6, 2004

Messages

2,101

• Nov 22, 2007

• #1

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

AWF VIP

Local time

Today, 08:16

Joined

Mar 23, 2006

Messages

2,592

• Nov 22, 2007

• #2

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

 

SQL_Hell

SQL Server DBA

Local time

Today, 16:16

Joined

Dec 4, 2003

Messages

1,360

• Nov 23, 2007

• #3

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

Something in here

Local time

Today, 08:16

Joined

Apr 6, 2004

Messages

2,101

• Nov 27, 2007

• #4

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

SQL Server DBA

Local time

Today, 16:16

Joined

Dec 4, 2003

Messages

1,360

• Nov 27, 2007

• #5

Hi there


Does it work now then?

 

SQL_Hell

SQL Server DBA

Local time

Today, 16:16

Joined

Dec 4, 2003

Messages

1,360

• Dec 4, 2007

• #6

Hi,

I guess I wasn't much help here

 

Pauldohert

Something in here

Local time

Today, 08:16

Joined

Apr 6, 2004

Messages

2,101

• Dec 4, 2007

• #7

Sorry yes - it now appears to be working OK.

Thanks

 

B

Banana

split with a cherry atop.

Local time

Today, 08:16

Joined

Sep 1, 2005

Messages

6,318

• Dec 4, 2007

• #8

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:

Code:Copy to clipboard

Do until qdf.StillExecuting=False
    'Do nothing
Loop

Read the help file for more information on that property.