VBA stops in middle of SQL routine

ARoyT

New Member
Joined
May 18, 2013
Messages
7
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:

Code:
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:

Rich (BB code):
    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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

Code:
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
 
Last edited by a moderator:
Upvote 0
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.

Rich (BB code):
    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!
 
Upvote 0
Solution

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top