VBA rest or pause between query execution ?

[carefree]

Hello,

I have a series of pass-through queries that flush (TRUNCATE) and fill (INSERT INTO) Global Temporary Tables in SAP HANA. When I run them individually/manually, they execute as intended and populate the tables accordingly. However, when I automate their execution with VBA, the TRUNCATE (clear table) pass-throughs work but only the first INSERT INTO query appears to execute as intended. The other insert statements do not (e.g. table structure intact but no data).

Is it possible that the VBA 'DoCmd.OpenQuery...' sequence is initiating all pass-throughs at once causing each successive pass-through query in the sequence to be interrupted? If so, is there a VBA solution that will pause each successive query from executing until the preceding one has completed?

Thanks for any input and insight.

 

[jdraw]

Can you copy your vba here in a post? You may get more focused responses.
You might consider DoEvents between the DoCmd.OpenQuery statements, but that's a guess at the moment.

 

[Minty]

I would ensure you set a reference to CurrentDB e.g.

Dim db as Database
Set db = CurrentDb

Then use something like

db.Execute "YourQuery", dbSQLPassThrough + dbFailOnError
DoEvents

db.Execute "Your2ndQuery", dbSQLPassThrough + dbFailOnError
DoEvents

By specifying a single workspace( db ) it should ensure that the queries are run in a process that waits for a response from the server.

 

[CJ_London]

you might be better to use currentdb.execute rather than docmd.openquery. By all accounts it is faster

see these links for a comparison

VBA, CurrentDb.Execute vs DoCmd.OpenQuery - what is the difference?

DoCmd.RunSQL/OpenQuery v. Database.Execute - Post.Byes

Friends, This question is in reference to another thread, but one response in particular, in which one of the Experts recommended (here is a link to that post (https://bytes.com/topic/access/answers/971428-send-key-issue#post3818971)). Most folks who know me well on this forum know how much I...

bytes.com

 

[The_Doc_Man]

If you are using a DAO recordset, you might wish to check on the status of the query.

Recordset.StillExecuting property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

You can use the index on the left of that page to look at other properties of DAO recordsets that might be helpful in this context. However, I believe that if you want to do this, you might have to do as CJ suggests and use a db.execute method to launch the action query.

 

[carefree]

Excellent feedback everyone. Thanks you!

Give me time to attempt some of the insightful suggestions and I will post back if something works and what did.

 

[carefree]

Can you copy your vba here in a post? You may get more focused responses.
You might consider DoEvents between the DoCmd.OpenQuery statements, but that's a guess at the moment.

Here you go

Option Compare Database
Option Explicit

Private Sub RunDPORScript_Click()
On Error GoTo RunDPORScript_Click_Err

DoCmd.SetWarnings False

'---Clear Pre-existing Tables---

DoCmd.OpenQuery "1A_TRUNCATE_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "2A_TRUNCATE_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "3A_TRUNCATE_PODOC_SA_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "4A_TRUNCATE_PODOC_CON_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "5A_TRUNCATE_STO_TempTbl", acViewNormal, acEdit

'---Fill Pre-existing Tables---

DoCmd.OpenQuery "1E_INSERT_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "2E_INSERT_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "3E_INSERT_PODOC_SA_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "4E_INSERT_PODOC_CON_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "5E_INSERT_STO_TempTbl", acViewNormal, acEdit

'---Display Global Temp Tables---

DoCmd.OpenQuery "1D_DISPLAY_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "2D_DISPLAY_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "3D_DISPLAY_PODOC_SA_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "4D_DISPLAY_PODOC_CON_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "5D_DISPLAY_STO_TempTbl", acViewNormal, acEdit

RunDPORScript_Click_Exit:
Exit Sub

RunDPORScript_Click_Err:
MsgBox Error$
Resume RunDPORScript_Click_Exit

End Sub

 

[carefree]

I would ensure you set a reference to CurrentDB e.g.

Dim db as Database
Set db = CurrentDb

Then use something like

db.Execute "YourQuery", dbSQLPassThrough + dbFailOnError
DoEvents

db.Execute "Your2ndQuery", dbSQLPassThrough + dbFailOnError
DoEvents

By specifying a single workspace( db ) it should ensure that the queries are run in a process that waits for a response from the server.

Hello Minty,

Here is what I tried to run:

Dim db As Database
Set db = CurrentDb

DoCmd.SetWarnings False

db.Execute "1E_INSERT_PLANNED_ORDERS_TempTbl", dbSQLPassThrough + dbFailOnError
DoEvents

db.Execute "2E_INSERT_PLANNED_ORDERS_2_TempTbl", dbSQLPassThrough + dbFailOnError
DoEvents

db.Execute "1D_DISPLAY_PLANNED_ORDERS_TempTbl", dbSQLPassThrough + dbFailOnError
DoEvents

db.Execute "2D_DISPLAY_PLANNED_ORDERS_2_TempTbl", dbSQLPassThrough + dbFailOnError
DoEvents

Unfortunately when I ran this, I got an Access Error message telling me that my SQL was invalid. I clearly did something wrong in the VBA statement because my pass-through queries run fine manually. Input?

 

[isladogs]

1. Use Dim db As DAO.Database
2. Using db.Execute makes DoCmd.SetWarnings superfluous. Remove both of those lines
3. Try replacing the query name with the query SQL.
4. You MAY find the dbSQLPassThough isn't needed

 

[carefree]

1. Use Dim db As DAO.Database
2. Using db.Execute makes DoCmd.SetWarnings superfluous. Remove both of those lines
3. Try replacing the query name with the query SQL.
4. You MAY find the dbSQLPassThough isn't needed

Thanks for your input.

I'll try 1, 2, and 4 but replacing the predefined query with the actual SQL tucked into the VBA script is not possible due to the length of the SQL that I am running. I really have no other option but to use the predefined queries.

 

[isladogs]

Why isn’t it possible? How many characters are in your query SQL?

 

[CJ_London]

Doesn’t a passthrough query require the connection string?

 

[carefree]

Doesn’t a passthrough query require the connection string?

I have the connection string saved in the predefined pass through query.

 

[carefree]

Why isn’t it possible? How many characters are in your query SQL?

This is a multiple SQL report script that I am breaking up into constituent parts and run from Access. As a whole, from start to finish, the SQL is about 11k lines. Individual SQL statements range from 5k to 60k characters. It's quite extensive..

 

[cheekybuddha]

As a whole, from start to finish, the SQL is about 11k lines.

Unfortunately when I ran this, I got an Access Error message telling me that my SQL was invalid.

Quite likely you are just using references to form/report controls directly rather than concatenating their values in to the SQL.

With db.Execute, remove the DoCmd.SetWarnings False/True statements - they aren't necessary and hide any useful errors.

 

[Isaac]

If you are using a DAO recordset, you might wish to check on the status of the query.

Recordset.StillExecuting property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

You can use the index on the left of that page to look at other properties of DAO recordsets that might be helpful in this context. However, I believe that if you want to do this, you might have to do as CJ suggests and use a db.execute method to launch the action query.

Or use an ADO command exec etc..

Recordset for a truncate or insert query?

 

[CJ_London]

think what you need is to use the querydef

db.querydefs("1E_INSERT_PLANNED_ORDERS_TempTbl").execute dbSQLPassThrough + dbFailOnError

perhaps need to drop the dbFailonError

 

[cheekybuddha]

perhaps need to drop the dbFailonError

Not sure about that.

More likely drop the dbSQLPassThrough

 

[CJ_London]

hmm - did a quick google and yes, think you are right

Execute SQL Server Pass-Through Query From Access VBA

I have an UPDATE pass through query saved in Access 2007. When I double-click on the pass through query it runs successfully. How can I get this query to run from VBA? I'd like it to run when my "s...

stackoverflow.com