SQL VBA multiple statements in 1go

[Siyanna]

Hi All,

It' been a while since I have posted an SQL query with VBA on here Xenou and you have been a massive help to me always

My question is, how do you write a string where I can am doing multiple statements

I.e

So in essence, I am running 1 statement if you like and all of the statements get run one after another

Now how can I put the above into a an SqlString in Excel VBA and use that? Sub ADO_SQL() Dim cmd As ADODB.Command Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String Dim strDB As String Dim strlogin As String Dim strpass As String Dim connection_string As String Dim ws As Worksheet Set cmd = New ADODB.Command Set rs = New ADODB.Recordset Set cn = New ADODB.Connection Set ws = Worksheets("Sheet1") 'Loads database login details strDB = "MVWLDM" '(Schema Details) strlogin = "Test1" '(MyLogin UserId) strpass = "password" '(MyPassword) '//Connection string 'Provider connection_string = "Provider=OraOLEDB.Oracle.1;" & "Password=" & strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist Security Info=True" sSQL = [B][COLOR=#00FF00]???????[/COLOR][/B] '//Open a connection with the connection string With cn .ConnectionString = connection_string .Open End With '//Run SQL Command With cmd .ActiveConnection = cn .CommandType = adCmdText .CommandText = sSQL .Execute End With cn.Close '//Cleanup (optional) Set cn = Nothing Set cmd = Nothing strSQL = "" End Sub

<tbody>

[TR]
[TD][COLOR=#00FF00]This part is done 1st[/COLOR][/TD]
[/TR]
[TR]
[TD]
call master.drop_table ('drop table MI166_A')[/TD]
[/TR]
[TR]
[TD];[/TD]
[/TR]
[TR]
[TD][COLOR=#00FF00]This part is done 2nd[/COLOR]
[/TD]
[/TR]
[TR]
[TD]create table MI166_A as[/TD]
[/TR]
[TR]
[TD]([/TD]
[/TR]
[TR]
[TD]select  /*+ PARALLEL(a,32) */ [/TD]
[/TR]
[TR]
[TD]        *[/TD]
[/TR]
[TR]
[TD]from    CIS.TVP068ACTIVITY a[/TD]
[/TR]
[TR]
[TD]where   TS_COMPLETED is null[/TD]
[/TR]
[TR]
[TD])[/TD]
[/TR]
[TR]
[TD];[/TD]
[/TR]
[TR]
[TD][COLOR=#00FF00]This Part is done 3rd[/COLOR]
[/TD]
[/TR]
[TR]
[TD]create index IDX_MI166_A1 on MI166_A (NO_ACCOUNT, CD_COMPANY_SYSTEM)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD];
[COLOR=#00FF00]This Part is done last[/COLOR]
[/TD]
[/TR]
[TR]
[TD]create index IDX_MI166_A2 on MI166_A (NO_EMPL_ASSGN, CD_COMPANY_SYSTEM)[/TD]
[/TR]

</tbody><colgroup><col></colgroup>

 

[Kyle123]

In exactly the same way as you'd do it for one statement. What exactly isn't working?

 

[Siyanna]

Hi Kyle

I dont know why but its not liking the way i have done the syntax and is not recognising it.

This is how i have done it

strSQL = " call master.drop_table ('drop table MI166_A');" & _
             " create table MI166_A as" & _
                    " (select* /*+ PARALLEL(a,32) */" & _
                    " from CIS.TVP068ACTIVITY a" & _
                    " where TS_COMPLETED Is Null");" & _
             " create index IDX_MI166_A1 on MI166_A (NO_ACCOUNT, CD_COMPANY_SYSTEM);" & _
             " create index IDX_MI166_A2 on MI166_A (NO_EMPL_ASSGN, CD_COMPANY_SYSTEM);"

 

[james_lankford]

is drop_table an oracle function ?

call master.drop_table ('drop table MI166_A');

is there a reason not to do it this way ?

http://psoug.org/definition/drop.htm

https://wiki.cse.buffalo.edu/services/content/how-drop-oracle-table

 

[Siyanna]

Tbh - I am not sure wheter its an oracle or TSQL query. All i do know is that this is a statement we run on TOAD and it works fine therefore wanted to see if i could put that in Excel VBA and automate the SQL bit.

Hopefully someone else or Kyle will know. I am not very good with SQL

Thank You

 

[xenou]

Your sql contains some unusual syntax. It is possible that what works in Toad will not work with this type of connection. In particular there is the master.drop_table function mentioned above, and this one: (select* /*+ PARALLEL(a,32) */", which looks like another function call, not just a string literal. It's weird for SQL anyway, even for Oracle.

You can try to re-write using standard SQL statements if possible. If that doesn't work, you might want to try to find out more about how your Toad (?) system works and if it supports scripts.

 

[Siyanna]

Your sql contains some unusual syntax. It is possible that what works in Toad will not work with this type of connection. In particular there is the master.drop_table function mentioned above, and this one: (select* /*+ PARALLEL(a,32) */", which looks like another function call, not just a string literal. It's weird for SQL anyway, even for Oracle.

You can try to re-write using standard SQL statements if possible. If that doesn't work, you might want to try to find out more about how your Toad (?) system works and if it supports scripts.

Hi Xenou

I have found out that master.drop is a function that was created by someone in the company and is needed

The Parallel statement is an oracle statement and apparently it just makes the code run faster by breaking it to several pieces and running it

Im no expert in this so hopefully you can help me put this is Vba or rewrite it so VBA understands it

Many thanks

 

[Siyanna]

Any luck anyone please Xenou, Kyle ...Many Thanks

 

[james_lankford]

in access you can make a query a
passthrough query

SQL Server Passthrough query stored into an Access VBA recordset - Stack Overflow

I never tried putting multiple sql statements into a passthrough query, but I think it should work

 

[xenou]

You test first with a simple sql statement. For instance, just "SELECT Count(*) FROM Table1;"
This is how you confirm that the connection is working and the sql is being processed. Then you work up in pieces from there adding more complex parts to your query. For instance, avoid that parallels stuff and custom functions for now.

In short, you don't even know if anything is working at all yet. It might be you are using the wrong driver, or a bad password, or just incorrect sql syntax.

Also, pass through query should work and is also a good idea - but probably it uses ODBC so none of that weird Oracle stuff there - just bog standard SQL.