Split Tables

[Bonediggler1]

Hi-

I have a table with 10,000 records. I use values from one field in this table to populate an "IN" clause in a SQL Pass-Through query.

However, as you know the IN clause can only handle 1000 iterations. As such, I would like to split the table into 10 sub tables and pass the values from each table into the IN clause one by one (pass in 1000 values, run the query, append results to a different table, clear the query, pass in the next 1000 values, etc etc.)

I am looking for a way to split this table via VBA. If anybody has any suggestions for handling the 1000 iteration limit on an IN clause, that would be helpful as well.

Thank you!

 

[MichaelRed]

Add a temporary (computed) field possibly a "SetId" which would designate the new subtable for the records. Populate this with a value. Use this ion the criteria for the export.

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!
DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!

DO NOT do this! Relational databases will be unkind to you and your successors for seve generations to come. Is the Grossest and worst case of denormalization imaginable!!!



MichaelRed

 

[PHV]

Why using the IN operator instead of a JOIN ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bonediggler1]

Thanks PHV but can i do this in a pass through query?

I would need to join a table from the Access db with a table from the Oracle db (heterogeneous query) which I don't think is possible, at least in Access 2003.

 

[PHV]

You may join a local access table with a linked oracle tabla.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bonediggler1]

You are correct. Thanks for this insight, not sure why I didn't think of it before!