VBA stops in middle of SQL routine

Status
Not open for further replies.

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:

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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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