ADODB Oracle... getting dupes..

StaticFX

New Member
Joined
Mar 23, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Just to add to this craziness...
I have edited the SQL to use DISTINCT and I dont not get dupes on my machine, another coworker does not get dupes either...
but one use IS getting dupes.. but not an entire set of dupes. They are getting about 100 duplicate rows.

I have looked and they have no other add-ins interfering .. using the same version of excel. I stepped through the code.. the SQL is correct..
No matter what I try, they get dups! I even looped through the recordset and output i field at a time.. DUPES!!

Anyone have ANY clue why one user would get dupes and others dont?
 
Upvote 0
Another update.
its ALWAYS at row 101 that the duplicates start????
HELP!!
 
Upvote 0
Did you figure out the issue? I'm facing the same thing now and can't figure out the problem!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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