SQL Query stops running when sent from Access VBA

Question

I have an access vba code which runs a query in SQL Server 14.0. However it gets about 20 minutes through before just stopping the query and moving onto the next line of my vba code.

If I copy the code directly into SQL and run it there it runs properly with no issues. I originally thought this was due to either a connection or command timeout, however usually this should have generated an error message (and even so I have set the timeouts to 0 to force them to run indefinitely).

Please find my code below, it uses a Cursor to loop through a range of tables to complete the same task. This task involves looping through a Do While until the conditions are met. At this point the loop should finish and start on the next table.

It does complete 3 of the cursor loops, but then it starts the next one and then just stops.

Hopefully the code makes sense, it is quite lengthy in access and I have had to split it all up using line breaks to make my SQL string easier to decipher and (attempt) to debug.

''...Declare all relevant variables above...

On Error GoTo 0

SQL_STR1 = "DECLARE @STRSQL AS Varchar(max) " & vbCrLf & _
           "DECLARE @TableName VARCHAR(50) " & vbCrLf & _
           "DECLARE LoopVal CURSOR FOR SELECT TableName_ FROM [DBASE_NAME].[dbo].[a_Base_Year_Matrices] " & vbCrLf & _
           "OPEN LoopVal " & vbCrLf & _
           "FETCH NEXT FROM LoopVal INTO @TableName " & vbCrLf & _
           "WHILE @@FETCH_STATUS = 0 " & vbCrLf & _
           "BEGIN " & vbCrLf & _


SQL_STR2 = "SET @STRSQL = " 

          '...Create Some Tables...

           "WHILE (@COUNTT < 1959 AND @COUNTTER < 150)" & vbCrLf & _
           "BEGIN" & vbCrLf & _

          '...Runs loops to meet conditions, once met drop unneeded tables and create final output...


SQL_STR3 = "EXEC(@STRSQL) " & vbCrLf & _
           "FETCH NEXT FROM LoopVal INTO @TableName " & vbCrLf & _
           "END " & vbCrLf & _
           "CLOSE LoopVal " & vbCrLf & _
           "DEALLOCATE LoopVal "

SQL_ALL = SQL_STR1 + SQL_STR2 + SQL_STR3

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Set SQL Server Location
cnn.ConnectionTimeout = 0
cnn.Open "Driver={SQL Server};Server=" & ServerName & ";Trusted_Connection=Yes;"
Set rs.ActiveConnection = cnn

DoCmd.SetWarnings False
cnn.CommandTimeout = 0

''Code to check to paste directly into SQL
''Debug.Print SQL_ALL

rs.Open SQL_ALL, cnn, adOpenForwardOnly

Next b

Next a

End Sub

Have you tried using a more modern driver? {SQL Server} is ancient, it's the default driver that came with Windows 98. While it has been updated a bit, it's no longer supported for new development. You can download a new one here — Erik A
– Erik A, Commented Oct 31, 2018 at 18:02
We may need to see the full T-SQL script (which by the way, VBA can read directly from a text file into a string to avoid concatenation and line breaks). Also, consider using a stored procedure for complex routines. Finally, cursor loops are not the recommended approach for a set-based, declarative language like SQL. You may not need loops or run them at application layer (i.e., VBA)! — Parfait
– Parfait, Commented Oct 31, 2018 at 18:07
@ErikvonAsmuth I must admit I know nothing about drivers. I'll download it and give it a go though. — Gavin
– Gavin, Commented Oct 31, 2018 at 19:29
@Parfait I didnt know that about cursors, I know they are a bit of a pain to set up, but is there a technical reason they aren't the preferred methodology? Now that I've found out about stored procedures I'm going to give that a try. I assume it is run in the same style as my current code, just that the loop this time is in VBA? — Gavin
– Gavin, Commented Oct 31, 2018 at 19:32
Thanks @Parfait. The answer below works, which I will use to hit my deadline and keep the client and PM off my back. I'm going to change our process over into a stored procedure over the weekend. Thanks for the offer of help to format the code, I'll attempt setting it all up myself first, I like to try things myself first before getting too much help. — Gavin
– Gavin, Commented Nov 1, 2018 at 9:25

ArcherBird · Accepted Answer · 2018-11-01 13:10:51Z

3

Start your SQL script with the SET NOCOUNT ON; Statement;

This will supress all server messaging and allow the result set into the recordset you are opening.

....
SQL_STR1 = "SET NOCOUNT ON; " & vbCrLf & _
           "DECLARE @STRSQL AS Varchar(max) " & vbCrLf & _
           "DECLARE @TableName VARCHAR(50) " & vbCrLf & _
           "DECLARE LoopVal CURSOR FOR SELECT TableName_ FROM [DBASE_NAME].[dbo].[a_Base_Year_Matrices] " & vbCrLf & _
           "OPEN LoopVal " & vbCrLf & _
           "FETCH NEXT FROM LoopVal INTO @TableName " & vbCrLf & _
           "WHILE @@FETCH_STATUS = 0 " & vbCrLf & _
           "BEGIN " & vbCrLf & _
....

SET NOCOUNT ON suppresses the "xx rows affected" message after any DML. This is a resultset and when sent, the client (your ADO Recordset in this case) must process it. Put simply, a recordset object does not handle these server messages - it is expecting a cursor that represents records from a base table, the results of a query, or a previously saved Recordset. When your recordset gets server messages back, it will simply give you an empty recordset. I would guess your VBA is not expecting an empty recordset and you have some block of code that is getting stuck when this condition happens? Hard to say without seeing ALL the VBA.

Setting NoCount ON makes the server skip to the good stuff - in this case the results of a query - which the recordset object IS meant to handle.

edited Nov 1, 2018 at 13:10

answered Oct 31, 2018 at 17:39

ArcherBird

2,1441 gold badge14 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gavin Over a year ago

Thanks that works. Not quite sure I understand why though. Are you able to explain what turning off the "No. of Rows affected" message does, and why that would allow the code to keep running?

ArcherBird Over a year ago

@Gavin - I couldn't fit my comments in a comment, so I added them to the answer. Hope it helps :-)

Collectives™ on Stack Overflow

SQL Query stops running when sent from Access VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related