>Here is a problem I am sure has been solved many times over.
>
>I have a local table A with a few hundred records that I want to join to a remote table B holding several million records. Running VFP 5.0
>
>Problem 1: When I CREATE the remote view (programatically) as SELECT * FROM B, it goes away and seems to actually populate the view. At least the odometer says so. Millions and millions and way too much time. How do I stop this from happening ?
>
>Problem 2: When I try to build the Local+Remote view using the View
>Designer, it blows up. Is this common ?
>
>Problem 3: Is there a strategy or syntax that I can use to optimize the Local+Remote view so that it does not pass all of the Remote View over to the Client side ? I currently use SELECT remoteview.* from
>localview,remoteview where localview.a=remoteview.a
>
>Thanks for any help.
I would create a parameterized remote view using a key field or whatever field from the local table as the parameter to limit the records returned to the remote view. Then you can create a local view of the local table and the remote view using whatever kind of join makes sense. If the local view needs to be requeried because the parameter for the remote view changes, you have to requery the remote view first, the requery the local view.
The View Designer blows up when you try to joind more than 2 tables because the VD nests the joins. You can get your initial code from the view designer, but you need to copy and paste that code to a PRG and tweak it to un-nest the joins. They have to wind up looking like:
JOIN ... ON ...;
JOIN ... ON ...
instead of:
JOIN ... ;
JOIN ... ;
ON ... ;
ON ...
Mark McCasland
Midlothian, TX USA