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

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

 

[ARoyT]

Apologies, I'd like to delete this thread as it isn't formatted as per the sites standards. I've created another thread with the code properly annotated.

 

[Joe4]

Re-posted: https://www.mrexcel.com/forum/excel-questions/1110629-vba-stops-middle-sql-routine.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).