SQL VBA multiple statements in 1go

[Siyanna]

[TABLE="width: 64"]
<tbody>[TR]
[TD]Hi All,[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]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[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]My question is, how do you write a string where I can am doing multiple statements[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I.e[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]

[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]

[TR]
[TD][/TD]
[/TR]
[TR]
[TD]So in essence, I am running 1 statement if you like and all of the statements get run one after another[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]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

[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[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.