Querying both a VFP Table and SQL Table at the same time.

[501blues]

Is there a way to query a VFP Table and a SQL Table at the same time. I am new with querying sql database and figured how to query a SQL table using SQLEXEC, but I want to run it against a VFP file. I am not even sure I am phrasing this correctly.

I have a table called "TodaysCanc.DBF" which contains a field called "POL_NUM". Todayscanc is located on my local drive and is a VFP table. On the server I have a database called Policies and a table called lienholders with a field called Pol_Num. Once I make my connection to the Policies database can join the TodaysCanc table and the Lienholders table using the common field of Pol_num? Can someone show me basically how to accomplish this basic task.


Thanks in advance.

 

[alvechurchdata]

Are your Fox tables in a database? If you set up a remote view into the SQL database then you can treat it just as though it's a Fox table.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

501Blues,

You say that you are using a "SQL table". I assume this is on a back-end database (otherwise you wouldn't be using SQLEXEC(), presumably). It would help a lot to know which back end.

In fact, could I make this plea to everyone asking questions in this section:
When you have a problem involving a back end database server, please specify what type of database server it is.

The reason I am stressing is that different back ends have different ways of doing what you want to do. I can tell you how to do it in SQL Server, but that won't help if you are using, say, MySQL.

That said, by far the simplest solution is to break the query into two parts. Retrieve all the required data from the server into a local cursor, using SQLEXEC(). Then, join that cursor with your VFP table using a SELECT within VFP.

That might be less efficient than doing the whole thing in one query, but it will be a lot easier.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[thepink]

everything seem to work except for memo fields that come across with weird characters after each letter