Ok, im stumped.
I have 2 functions...
1 - creates a batch in the DB and downloads it
2 - just re-downloads the batch
the first function opens a connection, runs a stored procedure (adds an entry into a table and gets a batch number) - then uses that batch number to pull from a view
the second function is fed the batch number and then pulls the data from the view
BOTH functions use the exact same query to pull the data...
Query one gets some duplicates
Query two doesnt
then both use this:
could running the stored proc and leaving that connection open do that?
Makes ZERO sense to me...
I have 2 functions...
1 - creates a batch in the DB and downloads it
2 - just re-downloads the batch
the first function opens a connection, runs a stored procedure (adds an entry into a table and gets a batch number) - then uses that batch number to pull from a view
the second function is fed the batch number and then pulls the data from the view
BOTH functions use the exact same query to pull the data...
Query one gets some duplicates
Query two doesnt
VBA Code:
cn.Open "Provider=OraOLEDB.Oracle;Data Source=carr_sand;User Id=" & Login(0) & ";Password=" & Login(1) & ";"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = SCHEMA & ".XR_CREATE_" & chg & "_OUTBOUND"
Set BID = cmd.CreateParameter("@OUTBOUNDBATCHID", adNumeric, adParamOutput)
cmd.Parameters.Append BID
Set TS = cmd.CreateParameter("@TS", adVarChar, adParamOutput, 50)
cmd.Parameters.Append TS
cmd.Execute
then both use this:
VBA Code:
Set wsMRC = wkb.Sheets(1)
Set rs = New ADODB.Recordset
SQL = "SELECT * FROM " & SCHEMA & ".XR_" & chg & "_OUTBOUND WHERE Header = 1 OR OUTBOUND_BATCH_ID = " & BatchID
wsMRC.Activate
rs.Open SQL, cn, adOpenStatic, adLockReadOnly
wsMRC.Range("A1").CopyFromRecordset rs
could running the stored proc and leaving that connection open do that?
Makes ZERO sense to me...