Alternative to using Seek method on Oracle DB for multi-column search 1

[ejm8]

What is the best way to search a recordset on multiple columns if the Seek method is not supported?

We're moving a growing table out of an Access MDB and into an Oracle DB. Every day 500-3,000 records are processed from a flat file and compared against what's in this table and records are added/deleted/updated based on critera that needs VBA processing. The primary key in the table consists of 3 fields. I had been running through the flat file and comparing it's records to the table records using Seek, but it doesn't seem Oracle ODBC drivers support Seek.

The Find method isn't an option because the search critera is 3 fields. The only way I know to do this without seek is using SQL statments, which would mean opening and closing the recordset thousands of times to process the file.

 

[Golom]

Have a look at the ADO filter method. It supports multiple field constraints.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ejm8]

Filter doesn't seem to be an option unless there's some way to optimize a recordset for Filter use. I've tried it three times and Access literly freezes for about 5 minutes when applying the filter (for a single search).

 

[ejm8]

Using a client side cursor seems to make a huge difference, so Filter may be fine.

Thanks for your help Golom!

 

[Golom]

You're welcome. Guess I should have asked what kind of cursor you were using first.

Glad you got it to work.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]