Can i shorten this multiple create/drop statement and run it once

[Siyanna]

Hi GUYS

Can I shorten this so it runs once and not having to run the command update several times?

Many Thanks

Sub RunBatch()
      
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("Outstanding")
Application.ScreenUpdating = True
   
  'Loads database login details
    Database = "MYDB"     '(Schema Details)
    UserId = "TEST1' '(MyLogin UserId)"
    Password = "TEST2" '(MyPassword)
    
    '//Connection string  'Provider
    connection_string = "Provider=OraOLEDB.Oracle.1;Password=" & Password & ";Persist Security Info=False;User ID=" & UserId & ";Data Source=" & Database & ";Extended Properties=PLSQLRSet=1;DistribTX=0;OLE DB Services = -1"
                        
    'On Error GoTo ConnectionError:
    
        With cn
            .ConnectionString = connection_string
            .Open
        End With

Varname1 = ""
Varname1 = Varname1 & "call master.drop_table ('drop table MI166_A') "
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname1
            .Execute
        End With
Varname11 = ""
Varname11 = Varname11 & "create table MI166_A as " & vbCrLf
Varname11 = Varname11 & "( " & vbCrLf
Varname11 = Varname11 & "select  /*+ PARALLEL(a,32) */ " & vbCrLf
Varname11 = Varname11 & "        * " & vbCrLf
Varname11 = Varname11 & "from    CIS.TVP068ACTIVITY a " & vbCrLf
Varname11 = Varname11 & "where   TS_COMPLETED is null " & vbCrLf
Varname11 = Varname11 & ") "
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname11
            .Execute
        End With
Varname12 = ""
Varname12 = Varname12 & "create index IDX_MI166_A1 on MI166_A (NO_ACCOUNT, CD_COMPANY_SYSTEM) "
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname12
            .Execute
        End With
Varname13 = ""
Varname13 = Varname13 & "create index IDX_MI166_A2 on MI166_A (NO_EMPL_ASSGN, CD_COMPANY_SYSTEM)"
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname13
            .Execute
        End With
End Sub

 

[xenou]

Can I shorten this so it runs once and not having to run the command update several times?

Hi,
There is no update command in your code. Not sure what you are asking.

 

[Siyanna]

Sorry - i meant execute command

im running 4 execute commands straighto drop table and the create a table and 2 indexes

can i shorten the code so it runs the 4 statements using 1 execute command?

 

[Joe4]

Well, you need to execute it for each SQL command you are running. There isn't really a way around that.
Though, you could store the different SQL strings in a table or an array and loop through, so you can remove redundancy in your code and shorten (it would still run the execute command 4 times, though you do not have it explicitly written in your code 4 times).

However, to take a step back there, why do you have code to do this? How (and how often) would you need to run this code? Normally, you shouldn't have a need for create a table more than once.

 

[Siyanna]

Hi - thank you

whay do you mean by store in table or array - is there any chance you could show us an example please?

I have to create this temp table on a daily basis as the master table gets updated daily therefore its a work requirement to create this temp table and uae this table to get more info

the code is run every day

 

[Joe4]

I have to create this temp table on a daily basis as the master table gets updated daily therefore its a work requirement to create this temp table and uae this table to get more info

the code is run every day

I have a different proposal which I use that seems to work better.
Instead of deleting/creating tables and indexes (which are a pain) every day, create your tables just once.

Then, instead of deleting/recreating the tables, just delete the data out of the tables, and leave the structure of the tables intact.
Then you never have to worry about re-establishing indexes, relationships, queries, etc.

 

[xenou]

Just a note, you don't have to reset the connection each time. just the new command text.

Don't know about Oracle specifically but possibly you can create one statement with four commands. Then just send it one time. You would want the four commands to be separated by a semicolon:

ie:

Drop table foo; create table foo (ID int); insert into foo values (1);

This wouldn't work in Access but might work in Oracle. In sql server you might not even need the semicolons. You'd have to test it.

Also by the way you could use a passthrough query and just send command text as well, without using ado. THen you just write the statements you want to execute in your sql and send it to Oracle (doesn't even need code).

I agree with Joe, by the way - if the table is being recreated every day, just leave it there and only delete the data instead of dropping it:
https://www.techonthenet.com/oracle/truncate.php

 

[Siyanna]

Sorry how would i do that?

how do i overide the existing data in the temp table as each day the naster table i am creating this temp table from changes everyday

if you could give us a code example i would really appreciate that

 

[Siyanna]

Hi Xenou- passthrough query, whats that?

 

[xenou]

A detailed explanation of Pass Through query:
MS Access for the Business Environment: Extend Access with Pass-Through Queries - Page 2 — DatabaseJournal.com
Shorter explanation (this is what I normally do):
https://kb.blackbaud.com/articles/Article/52445

As far as your question about overriding existing data in the temp table, that's easy. You will first delete all records in the table. Now it is "empty". then you run your other queries without having to use drop table, create table, or create index.