I have always consider to be a design problem someone needs to load 200,000 records! Revise this and problems should be solved.
>Greetings to all!
>
>We just recently switched from DAO to ADO with Access 2000 Mdb's. Now we are having some problems with regard to loading a table containing 200,000+ records.
>
>These records, by the way, are used to browse the entire product line for a store/company, and is a required feature in the system. And during development, the Mdb file is stored locally, but once deployed, it will be accessed through a network.
>
>Initially we used a CursorLocation of adUseClient (due to multiple users browsing at the same time) and CursorType of adOpenDynamic (to reflect changes by other users). This setup took an average of 5 minutes to load.
>
>We then tried adUseServer and adOpenStatic, respectively, just for comparison and managed to cut down loading to about 20 seconds. But this is not an ideal setup for our case.
>
>Maybe there's something we're not doing right with ADO, but we wish to exhaust all possible options before considering a change of tactics (like changing databases!).
>
>Many thanks in advance!
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com