Excel VBA ADODB SQL connection doesn't run the whole execute command

Question

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.

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.

I have no idea why this bizarre problem occurs. If anyone can help me I'll be most appreciated.

SQLStr = "DECLARE @newdatetime datetime; " & _
        "DECLARE @newdatetimelong datetime; " & _
        "DECLARE @timeint int; " & _
        "DECLARE @newficheno varchar(17); " & _
        "DECLARE @newlogref int; " & _
        "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); " & _
        "UPDATE L_LDOCNUM SET LASTASGND=@newficheno FROM L_LDOCNUM WHERE FIRMID=500 AND APPMODULE=1 AND DOCIDEN=12; " & _
        "SET @newdatetime=CONVERT(datetime,'" & DateInp & "',104); " & _
        "SET @timeint=LOGODB.dbo.LG_TIMETOINT(DATEPART(hour,GETDATE()),DATEPART(minute,GETDATE()),DATEPART(second,GETDATE())); " & _
        "SET @newdatetimelong=@newdatetime+LOGODB.dbo.LG_INTTOTIME(@timeint); " & _
        "INSERT INTO LG_500_01_STFICHE " & _
        "SELECT 3,12,3, " & _
        "@newficheno, " & _
        "@newdatetime, " & _
        "@timeint, " & _
        "'','','','',0,0,0,0,0,0,'',0, " & _
        "0,/*SOURCEINDEX*/ " & _
        "0,0,0,/*SOURCECSTGRP*/ " & _
        "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*/ " & _
        "3,0,'',0,0,1,0,0,0,0,0,0,'" & Worksheets("Form").Range("M1").Text & "',/*DOCTRACKINGNR*/ " & _
        "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, " & _
        "0,0,'',0,'','',NULL,0,0,0,'','','','',0,0,'',0,NULL,0,NULL,0,0,0,0,0,0; " & _
        "SET @newlogref=(SELECT LOGICALREF FROM LG_500_01_STFICHE WHERE TRCODE=12 AND FICHENO=@newficheno);"
    
Startsearch = Worksheets("Form").Range("A3").Address
        
 Do While IsNumeric(Worksheets("Form").Range(Startsearch).Value) And Worksheets("Form").Range(Startsearch).Value <> ""
             
  SQLStrL = SQLStrL & " INSERT INTO LG_500_01_STLINE " & _
    " SELECT " & _
    CDbl(Application.VLookup(Worksheets("Form").Range(Startsearch).Offset(0, 1).Text, Worksheets("Destek").ListObjects("Malzemeler").Range.Offset(0, 2), 5, False)) & "," & _
    " 0,0,0,0,12,@newdatetime,@timeint,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,@newlogref, " & _
    CDbl(Worksheets("Form").Range(Startsearch).Value) & "," & _
    " 0,0,0,0,0,0,0,406,0,0,0,0,0,0,0,'','', " & _
    Replace(CDbl(Worksheets("Form").Range(Startsearch).Offset(0, 12).Value), ",", ".") & "," & _
    " 0,0,0,0,0,0,1,0,0,0,0,0,'" & Worksheets("Form").Range(Startsearch).Offset(0, 6).Text & "', " & _
    CDbl(Application.VLookup(CDbl(Application.VLookup(Worksheets("Form").Range(Startsearch).Offset(0, 1).Text, Worksheets("Destek").ListObjects("Malzemeler").Range.Offset(0, 2), 4, 0)) & "-" & Worksheets("Form").Range(Startsearch).Offset(0, 13).Text, Worksheets("Destek").ListObjects("UnitSet").Range, 5, 0)) & "," & _
    CDbl(Application.VLookup(Worksheets("Form").Range(Startsearch).Offset(0, 1).Text, Worksheets("Destek").ListObjects("Malzemeler").Range.Offset(0, 2), 4, False)) & "," & _
    " 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, " & _
    " DATEPART(month,@newdatetimelong),DATEPART(year,@newdatetimelong), " & _
    " 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; " & vbNewLine
            
   
    
            Startsearch = Worksheets("Form").Range(Startsearch).Offset(1, 0).Address
          Loop
          
          
     
        Set cn = New ADODB.Connection
        cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
        
        
        
        cn.Execute SQLStr & SQLStrL, , ADODB.ExecuteOptionEnum.adExecuteNoRecords

Excel VBA ADODB SQL connection doesn't run the whole execute command

0 Answers