VBA stops in middle of SQL routine

[ARoyT]

Hi, I have VBA routine that calls several sub routines. The subroutines link to oracle and execute .sql scripts. When I run the sub routines individually they run fine, however, when I run the call routine it stops running in the midst of a couple of the sub routines, specifically 'Call p122_2_edam_1_2_4_1_devA' and 'Call p122_4_edam_2_2_1_2_nocomA'.

This is my call routine:

Sub Run_AM_Scripts()

    Call p122_0_a_compo
    Call p122_0_b_edam
    Call p122_1_composite_1_2_2_0_nocom
    Call p122_2_edam_1_2_4_1_devA
    Call p122_2_edam_1_2_4_1_devB
    Call p122_2_edam_1_2_4_1_devC
    Call p122_2_edam_1_2_4_1_devD
    Call p122_3_composite_2_2_1_2_nocom
    Call p122_4_edam_2_2_1_2_nocomA
    Call p122_4_edam_2_2_1_2_nocomB
    Call p122_5_a_sms_1_0_0_0
    Call p122_5_b_sms_1_0_0_0
    Call p122_5_c_sms_1_0_0_0
    Call p122_5_d1_sms_1_0_0_0

End Sub
[COLOR=#333333]

[/COLOR]

This is one of the subs that stops partway through:

    Sub p122_2_edam_1_2_4_1_devA()
    
    Dim con As ADODB.Connection
    Dim ConnectionString As String
    Dim SQL_Commands As Variant
    Dim SQL_String As Variant
    Dim strFilename As String: strFilename = "\\mydirectorypath\p122_2_edam_1_2_4_1_devA.sql"
    Dim iFile As Integer: iFile = FreeFile
    
    Set con = New ADODB.Connection
        
    ConnectionString = "MSDASQL.1;User ID=********;password=********;Data Source=ORACLE**"
        
    con.Open ConnectionString
      
    Open strFilename For Input As #iFile 
    SQL_String = Input(LOF(iFile), iFile)
    Close #iFile 

    'this allows me run many queries in one script that are separated by a semicolon    
    SQL_Commands = Split(SQL_String, ";")
    For Each SQL_String In SQL_Commands
        con.Execute SQL_String
    Next SQL_String
        
    con.Close
    
    'this ticks a checkbox I've called CB_4A after the connection is closed 
    Worksheets("1. Running the code").Shapes("CB_4A").ControlFormat.Value = xlOn
    
    'this gives me a message to say that the code as run
    MsgBox "That's p122_2_edam_1_2_4_1_devA done"
    
    End Sub

Apologies in advance if this is unclear. I don't want to post my sql as it's very long.

Has anyone had a similar problem and do you have a solution? Thanks

 

[Legacy 98055]

VBA does not return specific error info but only a general app error with ADO. I think. In any case, here is your edited procedure that might shed some light on your problem. If not, we can dig a bit more.

Sub p122_2_edam_1_2_4_1_devA()    
    On Error GoTo Handler
    Dim con As ADODB.Connection
    Dim ConnectionString As String
    Dim SQL_Commands As Variant
    Dim SQL_String As Variant
    Dim strFilename As String: strFilename = "\\mydirectorypath\p122_2_edam_1_2_4_1_devA.sql"
    Dim iFile As Integer: iFile = FreeFile
    
    Set con = New ADODB.Connection
        
    ConnectionString = "MSDASQL.1;User ID=********;password=********;Data Source=ORACLE**"
        
    con.Open ConnectionString
      
    Open strFilename For Input As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] 
    SQL_String = Input(LOF(iFile), iFile)
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFile"]#iFile[/URL] 


    'this allows me run many queries in one script that are separated by a semicolon
    SQL_Commands = Split(SQL_String, ";")
    For Each SQL_String In SQL_Commands
        con.Execute SQL_String
    Next SQL_String
        
    con.Close
    
    'this ticks a checkbox I've called CB_4A after the connection is closed
    Worksheets("1. Running the code").Shapes("CB_4A").ControlFormat.Value = xlOn
    
    'this gives me a message to say that the code as run
    MsgBox "That's p122_2_edam_1_2_4_1_devA done"
    Exit Sub
Handler:
    Debug.Print con.Errors.Count
    Stop
    'will give some info on the first error, if any. use con.Errors(1).Description for the 2nd error, etc.
    Debug.Print con.Errors(0).Description
    End Sub

 

[ARoyT]

Greetings from across the pond Tom and many thanks for your interest and reply. I tried your code and it debugged on 'Stop'. An error message thrown up was "ORA -01013: user requested cancel of current operation". Searching for that I found this: https://www.tekstream.com/oracle-er...requested cancel,in response to other errors. I hadn't requested a cancel so surmised it was a timeout. This page gave me some useful info: http://codingjourney.blogspot.com/2008/11/ado-connection-timeout-command-or.html. I added "con.CommandTimeout = 60" to my code and it now runs from beginning to end. The previous script takes about 20 mins to run and I'm assuming this was the cause of the problem.

    Sub p122_2_edam_1_2_4_1_devAa()
    
    Dim con As ADODB.Connection
    Dim ConnectionString As String
    Dim SQL_Commands As Variant
    Dim SQL_String As Variant
    Dim strFilename As String: strFilename = "\\chifs12\vol03\SkyTalk\Repository\Revenue Assurance\Technical Compliance\Provisioning Accuracy\Address Matching\Daily Address Matching Report\Production Scripts\Current\VBA scripts\p122_2_edam_1_2_4_1_devAa.sql"
    Dim iFile As Integer: iFile = FreeFile
    
    Set con = New ADODB.Connection
        
    ConnectionString = "MSDASQL.1;User ID=*********;password=********;Data Source=ORACLE**"
        
    con.Open ConnectionString
    con.CommandTimeout = 60
    
    Open strFilename For Input As #iFile 
    SQL_String = Input(LOF(iFile), iFile)
    Close #iFile 
    
    SQL_Commands = Split(SQL_String, ";")
    For Each SQL_String In SQL_Commands
        con.Execute SQL_String
    Next SQL_String
        
    con.Close
    
    Worksheets("1. Running the code").Shapes("CB_4Aa").ControlFormat.Value = xlOn
    
    MsgBox "That's p122_2_edam_1_2_4_1_devAa done"
      
    End Sub

Thanks again!

 

[Legacy 98055]

Good for you. Have a nice day!