Create new recordset after 1000 rows retrieved

[Fattire]

I need to compare 4000 records in Access to another database (Oracle) using SQL via the IN function. It's limited to 1000 expressions, which means I need to split it up 4 times and run the SQL 4 times or create 4 IN statements in the SQL, which I would rather do so that I only run this query once.

So I extract all 4000 records from access into rec1

sqlstr = select eenum from tblEmployee
rec1.open, sqlstr, con1

This rec1 will have 4000 records in it (could have 2,000 to 20,000 at any given time, in that case I would need to create 20 strings of 1000 each ...woof)

Now I need to split rec1 into 4 strings of 1000 each:

empstr1 = ""

Do While Not rec1.EOF
    empstr1 = empstr1 & rec1!eenum & ","
    rec1.MoveNext
Loop
...and do this 4 times

empstr1 (first 1000 expressions strung together)
empstr2 (second 1000 expressions strung together)
empstr3 (third 1000 expressions strung together)
empstr4 (fourth 1000 expressions strung together)

I have code that will delete the last comma

And then I'll just plug the strings into the SQL that hits the Database:

SELECT EENUM
  FROM tblEmp
 WHERE EENUM IN(" & empstr1 & ")
    OR EENUM IN(" & empstr2 & ")
    OR EENUM IN(" & empstr3 & ")
    OR EENUM IN(" & empstr4 & ")

Any ideas or links on how to do this?

 

[Remou]

Why not:

1. SELECT TOP 1000 FROM tblEmployee 
   ORDER BY EENUM
2. SELECT TOP 1000 FROM tblEmployee 
   WHERE EENUM NOT IN (
          SELECT TOP 1000 FROM tblEmployee 
          ORDER BY EENUM)
   ORDER BY EENUM
3. SELECT TOP 1000 FROM tblEmployee 
   WHERE EENUM NOT IN (
          SELECT TOP 2000 FROM tblEmployee 
          ORDER BY EENUM)
   ORDER BY EENUM

And so on.

http://lessthandot.com

 

[Fattire]

That's clever Remou -why didn't I try that? Because that's too easy

So I did that this weekend and it works great!

Like you suggested, I basically just extracted the numbers that were left of the first 3 numbers in the recordcount (so if recordcount = 3900 it xtracted the '3') and looped that number as a Q = 1 to intCount (3) thing and plugged Q in the "NOT TOP (Q)000" section of the code as it would loop through each 1 thousand records.

Updated 3900 records in 3 minutes, not bad considering how slow our Oracle DB is.