title: Excel VBA ADODB SQL connection doesn't run the whole execute command
body: <p>Hey there I have a weird problem which I can't find its source. I have a messy code below which takes user input from one of the excel pages(kinda like a form) and creates a long sql query.</p>
  <p>Everytime SQLstrL command gets updated within the Do While it basically adds another INSERT command to the SQLstrL string.
  When I copy the sql query which VBA created via a function and paste on SSMS everything works like a charm. So I know there is nothing wrong with the command text. But when I try to execute from cn.Execute like below, It only executes first 4 INSERT commands of SQLstrL and ignores the rest. I have up to 60 INSERT commands within SQLstrL string.</p>
  <p>I have no idea why this bizarre problem occurs. If anyone can help me I'll be most appreciated.</p>
  <pre><code>SQLStr = &quot;DECLARE @newdatetime datetime; &quot; &amp; _
          &quot;DECLARE @newdatetimelong datetime; &quot; &amp; _
          &quot;DECLARE @timeint int; &quot; &amp; _
          &quot;DECLARE @newficheno varchar(17); &quot; &amp; _
          &quot;DECLARE @newlogref int; &quot; &amp; _
          &quot;SET @newficheno=(SELECT REPLICATE('0',LEN(LASTASGND)-LEN(LASTASGND+1))+REPLACE(STR(CONVERT(varchar,LASTASGND)+1),' ','') FROM L_LDOCNUM WHERE FIRMID=500 AND APPMODULE=1 AND DOCIDEN=12); &quot; &amp; _
          &quot;UPDATE L_LDOCNUM SET LASTASGND=@newficheno FROM L_LDOCNUM WHERE FIRMID=500 AND APPMODULE=1 AND DOCIDEN=12; &quot; &amp; _
          &quot;SET @newdatetime=CONVERT(datetime,'&quot; &amp; DateInp &amp; &quot;',104); &quot; &amp; _
          &quot;SET @timeint=LOGODB.dbo.LG_TIMETOINT(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE())); &quot; &amp; _
          &quot;SET @newdatetimelong=@newdatetime+LOGODB.dbo.LG_INTTOTIME(@timeint); &quot; &amp; _
          &quot;INSERT INTO LG_500_01_STFICHE &quot; &amp; _
          &quot;SELECT 3,12,3, &quot; &amp; _
          &quot;@newficheno, &quot; &amp; _
          &quot;@newdatetime, &quot; &amp; _
          &quot;@timeint, &quot; &amp; _
          &quot;'','','','',0,0,0,0,0,0,'',0, &quot; &amp; _
          &quot;0,/*SOURCEINDEX*/ &quot; &amp; _
          &quot;0,0,0,/*SOURCECSTGRP*/ &quot; &amp; _
          &quot;0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','','','','','',1,0,0,0,0,0,0,1,@newdatetimelong,DATEPART(hour,@newdatetimelong),DATEPART(minute,@newdatetimelong),DATEPART(second,@newdatetimelong),0,NULL,0,0,0,0,0,'','','',/*TRACKNR*/ &quot; &amp; _
          &quot;3,0,'',0,0,1,0,0,0,0,0,0,'&quot; &amp; Worksheets(&quot;Form&quot;).Range(&quot;M1&quot;).Text &amp; &quot;',/*DOCTRACKINGNR*/ &quot; &amp; _
          &quot;0,0,'',0,0,0,'',0,'',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,0,@newdatetime,@timeint,0,0,0,NEWID(),'',0,0,0,'',0,0,0,0,0,0,NULL,0,0,0,@newdatetime,@timeint, &quot; &amp; _
          &quot;0,0,'',0,'','',NULL,0,0,0,'','','','',0,0,'',0,NULL,0,NULL,0,0,0,0,0,0; &quot; &amp; _
          &quot;SET @newlogref=(SELECT LOGICALREF FROM LG_500_01_STFICHE WHERE TRCODE=12 AND FICHENO=@newficheno);&quot;
      
  Startsearch = Worksheets(&quot;Form&quot;).Range(&quot;A3&quot;).Address
          
   Do While IsNumeric(Worksheets(&quot;Form&quot;).Range(Startsearch).Value) And Worksheets(&quot;Form&quot;).Range(Startsearch).Value &lt;&gt; &quot;&quot;
               
    SQLStrL = SQLStrL &amp; &quot; INSERT INTO LG_500_01_STLINE &quot; &amp; _
      &quot; SELECT &quot; &amp; _
      CDbl(Application.VLookup(Worksheets(&quot;Form&quot;).Range(Startsearch).Offset(0, 1).Text, Worksheets(&quot;Destek&quot;).ListObjects(&quot;Malzemeler&quot;).Range.Offset(0, 2), 5, False)) &amp; &quot;,&quot; &amp; _
      &quot; 0,0,0,0,12,@newdatetime,@timeint,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,@newlogref, &quot; &amp; _
      CDbl(Worksheets(&quot;Form&quot;).Range(Startsearch).Value) &amp; &quot;,&quot; &amp; _
      &quot; 0,0,0,0,0,0,0,406,0,0,0,0,0,0,0,'','', &quot; &amp; _
      Replace(CDbl(Worksheets(&quot;Form&quot;).Range(Startsearch).Offset(0, 12).Value), &quot;,&quot;, &quot;.&quot;) &amp; &quot;,&quot; &amp; _
      &quot; 0,0,0,0,0,0,1,0,0,0,0,0,'&quot; &amp; Worksheets(&quot;Form&quot;).Range(Startsearch).Offset(0, 6).Text &amp; &quot;', &quot; &amp; _
      CDbl(Application.VLookup(CDbl(Application.VLookup(Worksheets(&quot;Form&quot;).Range(Startsearch).Offset(0, 1).Text, Worksheets(&quot;Destek&quot;).ListObjects(&quot;Malzemeler&quot;).Range.Offset(0, 2), 4, 0)) &amp; &quot;-&quot; &amp; Worksheets(&quot;Form&quot;).Range(Startsearch).Offset(0, 13).Text, Worksheets(&quot;Destek&quot;).ListObjects(&quot;UnitSet&quot;).Range, 5, 0)) &amp; &quot;,&quot; &amp; _
      CDbl(Application.VLookup(Worksheets(&quot;Form&quot;).Range(Startsearch).Offset(0, 1).Text, Worksheets(&quot;Destek&quot;).ListObjects(&quot;Malzemeler&quot;).Range.Offset(0, 2), 4, False)) &amp; &quot;,&quot; &amp; _
      &quot; 1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, &quot; &amp; _
      &quot; DATEPART(month,@newdatetimelong),DATEPART(year,@newdatetimelong), &quot; &amp; _
      &quot; 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,0,NEWID(),'',0,0,'','',0,0,0,0,'',0,'','',0,0,'','',0,0,0,0,NULL,0,0,0,0,0,0,'','',0,0,0,0,0,0,NULL,0,0,0,0,0,0,0,0,0,'','','',NULL,0,0,0,0,0,0,0,0,0; &quot; &amp; vbNewLine
              
     
      
              Startsearch = Worksheets(&quot;Form&quot;).Range(Startsearch).Offset(1, 0).Address
            Loop
            
            
       
          Set cn = New ADODB.Connection
          cn.Open &quot;Driver={SQL Server};Server=&quot; &amp; Server_Name &amp; &quot;;Database=&quot; &amp; Database_Name &amp; _
          &quot;;Uid=&quot; &amp; User_ID &amp; &quot;;Pwd=&quot; &amp; Password &amp; &quot;;&quot;
          
          
          
          cn.Execute SQLStr &amp; SQLStrL, , ADODB.ExecuteOptionEnum.adExecuteNoRecords
  </code></pre>